CODEWITHSUNDEEP
sql-serversql-server-2008
bhaskar
bhaskar

Reputation: 11

SQL Server 2008 shrink database

I have a 1 TB database file that I am cleaning up. I need to now shrink this file. I am currently running this statement:

USE [DW_Zone]--databasename
GO

DBCC SHRINKDATABASE(N'DW_Zone')
GO

I'm trying to shrink the database, but it has been running for over 3 days now, what is the issue?

If I stop this process then what will the effect on my database? And any other way to shrink this database?

Please help me...

Upvotes: 1

Views: 812

Answers (2)

Chris311
Chris311

Reputation: 3992

If I stop this process then what will the effect on my database?

DBCC SHRINKDATABASE operations can be stopped at any point in the process, and any completed work is retained.

Upvotes: 0

Vamsee Krishna
Vamsee Krishna

Reputation: 66

We can shrink only the transaction log file(.ldf) not the data(.mdf). You can use the following query to shrink your corresponding database.

USE [DBName]
GO
ALTER DATABASE [DBName] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(DB_log, 1)
ALTER DATABASE [DBName] SET RECOVERY FULL WITH NO_WAIT
GO

We can set the SQL jobs to run this query frequently whenever the log file reaches the maximum size(which we can set based on the disk space).

Upvotes: 1

Related Questions