Link to home
Create AccountLog in
Avatar of Albert Widjaja
Albert WidjajaFlag for Australia

asked on

Reducing the SQL Server database and logs smallest possible without data loss ?

Hi All,

I'm running the below database in older physical box:

Microsoft SQL Server 2012 - 11.0.5343.0 (X64) 
	May  4 2015 19:11:32 
	Copyright (c) Microsoft Corporation
	Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Open in new window


and planning to migrate it to different box using offline backup method (cold backup). Hence I need to shrink the SQL server files to reduce the manual copy time.

Without impacting or configuring the database size from the application using the SQL server, how can I shrink the SQL .MDF and .LDF file size ? I need some steps and guidance in this.

Is there any impact on the application when I shrink those databases files to the smallest possible ?

Thanks in advance.
SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Albert Widjaja

ASKER

Ah I see,

I prefer to use the GUI, is it using:

Right click at the DB Tasks > Shrink > Database
Right click at the DB Tasks > Shrink > Files (I assume this is the log files) ?

Is there any specific order to do it ?

DB first or log first ?
Guy,

What settings should I select for the DB files shrink (.MDF) below ?
User generated image
What settings should I select for the Log files shrink (.LDF) below ?
User generated image
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Cool,

I have just did select File Type: Logs and then selecting Release unused space and it went so quick, I can now see significant disk space released.

Can i do:

 File Type: Data and then selecting Release unused space again for all SQL server database.
you cannot do that "for all databases" unless you start scripting the commands (using dbcc shrinkfile)
Ah yes, but if I can do it manually one by one then it is not a problem right ?

I need to shrink both DB & logs to the smallest possible.
no problem to do it one by one (in the gui)
Thanks !
Hi Guy,

When I shrink the databse from GUI, I got this:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Shrink failed for Database 'CSR-DB'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.5058.0+((SQL11_PCU_Main).140514-1820+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Shrink+Database&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Lock request time out period exceeded.
Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.5343&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
This bascially means you still have users on the db, maybe "yourself"
this is sometimes tricky using the GUI, when you have some tables open in edit more or so, or simply an open transaction on the db.
you need to make sure everybody having some long/locking transcations gets logged off
Already did, the application services are all stopped.

I cannot even detach it and cannot Turn offline the database ?

TITLE: Microsoft SQL Server Management Studio
------------------------------

Drop all active database connections failed for Server 'PRODDB01'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.5058.0+((SQL11_PCU_Main).140514-1820+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+all+active+database+connections+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Only user processes can be killed. (Microsoft SQL Server, Error: 6107)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.5343&EvtSrc=MSSQLServer&EvtID=6107&LinkId=20476

------------------------------
BUTTONS:
OK
------------------------------
as I said, it may be "yourself".
in the GUI, if you expanded the database, or have an open sql command sheet open to the db (use <database>, it's already that.
you can check in the active sessions if it's really you.
make sure that your session is linked to another db, and then just right-click on the database in the GUI to start the operations
other than that, I would see replication, or sql agent jobs (backups?) running?
Yes, you are awesome :-)

After I close the SSMS and then re open without opening the script window, I can perform the Database Shrink succesfully.