I use this page myself.
DBDeving and DBAing are large and complex subjects, and sometimes it is good to have a little library of most commonly used snippets.
If you do not understand what the snippet does, or how it does it, DO NOT USE.
Snippets that beginners should really avoid will be marked with a warning sign and have a "WARNING" alert box (same as this text is in), describing some of the dangers involved in using the snippet.
You have been warnededed...
This is useful especially after restoring a DB from one server to another (say you are refreshing a QA box with the newest PROD data).
It is a relative safe operation, and will not touch or break existing and working DB users.
/*
Source: Various
Usage: Run as DBO on SQL2008R2 or newer.
Caveat: Update the USE statement for the DB that you are working on.
*/
-- USE [--INSERT DB NAME HERE--]
EXECUTE sp_change_users_login 'report' ; --See all Zombie users in the database.
GO
DECLARE @ZombieUsers TABLE
(
idKey Int IDENTITY(1,1) PRIMARY KEY,
UserName SysName,--nVarChar(128)
UserSID VarBinary(85)
)
INSERT INTO @ZombieUsers
EXECUTE sp_change_users_login 'report' ;
DECLARE @CRLF as nVarChar = CHAR(10) + '&' + CHAR(13)
DECLARE @Sql as nVarChar(MAX) = N''
DECLARE @idKey as Int = 1
DECLARE @MaxidKey as Int
SET @MaxidKey = ( SELECT COUNT(*) FROM @ZombieUsers ) ;
DECLARE @theCount as Int = 0
DECLARE @UsersFixed as nVarChar(MAX) = N''
DECLARE @UserName as SysName -- Zombied Database user.
WHILE ( @idKey <= @MaxidKey )
BEGIN
SET @UserName = ( SELECT UserName FROM @ZombieUsers WHERE idKey = @idKey ) ;
IF ( SELECT COUNT(*) FROM sys.server_principals WHERE Name = @UserName ) = 1
BEGIN
SET @Sql = @Sql + 'EXECUTE sp_change_users_login ''update_one'', [' + @UserName + '], [' + @UserName + ']' + @CRLF ;
SET @UsersFixed = @UsersFixed + @UserName + ', ' ;
SET @theCount = @theCount + 1 ;
END
SET @idKey = @idKey + 1 ;
END
PRINT @Sql ;
EXECUTE sp_executesql @Sql ;
PRINT 'Total fixed: ' + CAST(@theCount as VarChar) + '. Users Fixed: ' + @UsersFixed ;
SELECT ( 'Total fixed: ' + CAST(@theCount as VarChar) + '. Users Fixed: ' + @UsersFixed ) AS [Fixed] ;
GO
EXECUTE sp_change_users_login 'report' ; --See all Zombie users still in the database.
GO
/* END */
For many years the common concession from most DBA was that shrinking the TEMPDB was just... ugh.
As you know (I hope), TEMPDB is dropped and recreated every time the SQL server is restarted.
But most of us DBA try not to restart our prod server unless required - for various reasons.
And yes, if you set up your server correctly and TEMPDB friendly, then there will never be a reason to shrink it. But we all have those inherited servers that we cannot (easily) refactor.
So sometimes you just need to be able to shrink the TEMPDB - for example in that emergency where the app owners forgot to inform anyone of the massive import they are running overnight, which grows the TEMPDB beyond recognition on a server config that is not TEMPDB friendly.
PS: Microsoft recently officially wrote "Sure, go ahead and shrink TEMPDB if you want to."
Bonus Note: the first methd works well on any DB - just replace the file identifiers "TEMPDEV" and "TEMPLOG" with whatever your specific DB uses. And if your DB has more than just a MDF and LDF, just add a new line of "SHRINKFILE" for each additional file.
/*
Method one, try this first.
*/
USE [TEMPDB];
GO
DBCC FREESYSTEMCACHE('ALL');
GO
DBCC SHRINKFILE (TEMPLOG,0);
DBCC SHRINKFILE (TEMPDEV,0);
GO
If you need to move your TEMPDB, here is the simple way to do so.
Note: This script assumes you have a default TEMPDB setup.
/*
Get the old TEMPDB name, and edit below if needed.
*/
SELECT NAME, PHYSICAL_NAME AS CURRENTLOCATION
FROM SYS.MASTER_FILES
WHERE DATABASE_ID = DB_ID( N'TEMPDB' );
GO
USE MASTER;
GO
ALTER DATABASE TEMPDB
MODIFY FILE ( NAME = TEMPDEV,
FILENAME = 'D:\SQL_TEMP\TEMPDB.MDF');
GO
ALTER DATABASE TEMPDB
MODIFY FILE ( NAME = TEMPLOG,
FILENAME = 'D:\SQL_TEMP\TEMPLOG.LDF');
GO
/*
Restart SQL service.
Check if it worked!
*/
SELECT NAME, PHYSICAL_NAME AS CURRENTLOCATION, STATE_DESC
FROM SYS.MASTER_FILES
WHERE DATABASE_ID = DB_ID( N'TEMPDB' );
/*
Finally: Delete tempdb.mdf and templog.ldf in the old location.
*/
This just creates the script to drop either all tables or a filtered set, depending on our use case (some configuration required.)
The nice thing is the drop does not happen immediatly, so you can generate the DROP TABLE script for use in some future work.
/*
Should work SQL 2005 and up.
*/
SELECT
'DROP TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME +']'
FROM
INFORMATION_SCHEMA.TABLES;
GO
/*
And you can filter, if you need to, here just an example:
*/
SELECT
'DROP TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA IN ('DBO')
OR TABLE_NAME LIKE 'F01%'
;
GO
/*
Run this in MASTER.
*/
USE [master];
GO
DECLARE @USERMODE as nvarchar(100);
SET @USERMODE = (SELECT user_access_desc FROM sys.databases WHERE name = DB_NAME());
IF @USERMODE = 'SINGLE_USER'
BEGIN
PRINT '['+DB_NAME()+'] is in single user mode! ['+@USERMODE+']'
END
ELSE
BEGIN
PRINT 'No, ['+DB_NAME()+'] is in mode ->['+@USERMODE+'].'
END
GO
One of the few times I use cursors.
This handy script can even run in a batch or job on a schedule, when a proper backup system is missing.
Edit - and uncomment - the ignore filter as required.