Solved

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

Posted on 2016-08-22
14
62 Views
Last Modified: 2016-08-23
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.
0
Comment
  • 8
  • 6
14 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 41766377
the technical part is documented:
https://msdn.microsoft.com/en-us/library/ms190757.aspx
using the sql server dbcc shrinkfile command, you can do that (note: it's not a command line tool, but really a sql command, to be executed being connected in sql to that db. there is also the possibility to do that in the GUI, if you feel safer doing it that way
0
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 41766379
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 ?
0
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 41766381
Guy,

What settings should I select for the DB files shrink (.MDF) below ?
Database
What settings should I select for the Log files shrink (.LDF) below ?
Logs
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 41766389
shrink database will try to do the "shrink files" for all the files...
I prefer the "shrink files", as there I can see better what files to effectively shrink.

the checkbox to reorganize the file space should make sure that any free space in the middle of the file is "moved" towards the end of the file, as only there the "free space" can be released.
however, for especially large files that are highly "fragmented internally", and with low free space, this will not work, and is then not needed anyway.
=> the only way to get that "fixed" is to add new files to the filegroup(s), or usually better, add a new filegroup, move all the objects to that new filegroup, shrink then the original files, and move the objects back...
also, you may consider to anyhow add files to your filegroups if the files are getting so big, but this is a completely different story altogether.

in regards to the log files, you should make sure you run a full backup or a transaction log backup just before. a reorganization may work, but not always.
0
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 41766405
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.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41766412
you cannot do that "for all databases" unless you start scripting the commands (using dbcc shrinkfile)
0
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 41766427
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.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41766442
no problem to do it one by one (in the gui)
0
 
LVL 7

Author Closing Comment

by:Senior IT System Engineer
ID: 41766445
Thanks !
0
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 41766505
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
------------------------------
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41766507
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
0
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 41766519
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
------------------------------
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41766524
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?
1
 
LVL 7

Author Comment

by:Senior IT System Engineer
ID: 41766525
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.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A procedure for exporting installed hotfix details of remote computers using powershell
This article explains how to install and use the NTBackup utility that comes with Windows Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question