Archive

Archive for January, 2011

Restore SQL Server Database – disconnect all users

18 January, 2011 Leave a comment

Hi,

Often when you try to restore a database there will be connections to the DB, which will stop the restore from succeeding.

What I do is use the wizard to configure the restore, and then click the script button, to script the restore to the clipboard, so that I can run it as a TSQL statement.

The trick is you want to disconnect all sessions, so you send the Database into single user mode which will disconnect all sessions. After the restore you then set the database back to normal.

The code would look like

Alter Database YOURDB Set SINGLE_USER with Rollback Immediate
GO

RESTORE DATABASE [YOURDB] FROM  DISK = N'F:\SQLBackup\YOURDB.bak' WITH  FILE = 1, NOUNLOAD,  REPLACE,  STATS = 10
GO

Alter Database YOURDB Set MULTI_USER 
GO

The important bits are the Alter Database statements either side of the restore database script.

Categories: SQL Server

SQL Server SHRINKFILE not working

5 January, 2011 Leave a comment

As microsoft put in in SQL Server Books online, “A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation” (Note: all quotes are from SQL Server BOL)

The other day I blew away over 100GB of tables and issued the shrinkfile on both the Data and Log files and nothing happened.

Here is the reason…

If you use the SQL management tool and choose to shrink the data file releasing unused space you will get the following command executed:

DBCC SHRINKFILE (N'YOURDBNAME' , 0, TRUNCATEONLY)
GO

When you read about “Truncate Only” it says

Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent.

So depending on how the tables are allocated on disk inside the .mdf file it cannot release anything. Kind of like trying to partition a hard disk, you have to defrag the disk to move all the data to the front of the disk so you can make a big “contiguous” block at the end to repartition.

so really we want to essentially defrag our database file first, and then truncate, enter the “NOTRUNCATE” statement

DBCC SHRINKFILE (N'YOURDBNAME' , 0, NOTRUNCATE)
GO

Again SQL Server BOL says:

Moves allocated pages from the end of a data file to unallocated pages in the front of the file with or without specifying target_percent. The free space at the end of the file is not returned to the operating system, and the physical size of the file does not change.

Note: This is only applicable to the datafile, it does nothing on the log file
So what you really want to do is:

DBCC SHRINKFILE (N'YOURDBNAME' , 0, NOTRUNCATE)
GO
DBCC SHRINKFILE (N'YOURDBNAME' , 0, TRUNCATEONLY)
GO

This will essentially defrag your .mdf file and then truncateonly will shrink down to the last allocated extent.

I may be wrong but I think that is what the ShrinkDatabase attempts to do and hence why it appears to take so long.

DBCC SHRINKDATABASE(N'YOURDBNAME')
GO

My recommendation:

  1. Try to shrink the file first and see what gets released.
  2. If its not good enough then try ShrinkDatabase.

Note: Shrinking my database file of 100GB took 41minutes on a 16 CPU box with 32GB of memory
Cheers

Categories: SQL Server