Solved

sql server scripts needed

Posted on 2009-05-18
15
321 Views
Last Modified: 2012-05-07
does any body has script that runs weekly to shrink ldf files
0
Comment
Question by:aatishpatel
  • 7
  • 6
  • 2
15 Comments
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 380 total points
ID: 24411548
you have been given the scripts you need to do this in your previous posts.  you have EVERYTHING that you need already.
0
 
LVL 6

Assisted Solution

by:tangchunfeng
tangchunfeng earned 120 total points
ID: 24411569
define a backup job in sql server agent
when backup log file , you have the option to shrink the ldf


0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 380 total points
ID: 24411584
one of the same links I posted for you before....

http://blogs.techrepublic.com.com/datacenter/?p=448
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 6

Assisted Solution

by:tangchunfeng
tangchunfeng earned 120 total points
ID: 24411622
0
 

Author Comment

by:aatishpatel
ID: 24411709
ok thanx
0
 

Author Comment

by:aatishpatel
ID: 24412342
ok so
I have a problem shrinking the log file. The command that I use is
DBCC SHRINKFILE (IBM_log, 50)
But in result pane it shows:----  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [.        And  in
Message pane it shows :------- [SQLSTATE 01000] (Message 2528)  Cannot shrink file '2' in database 'P002' to 1310720 pages as it only contains 859656 pages. [SQLSTATE 01000] (Message 7993)  Cannot shrink log file 2 (NextGen_Log) because all logical log files are in use.
 And one more thing my log files are stored in L drive L:\logs\. In some databases I see that there is only one file ibm   but in some databases there are two files ld_1  and ld_2. So I do not understand why there are two files. Can somebody explain me what are these two files?  
And I have created weekly shrink log task:- the steps are as below please verify and let me know if its correct.
And they run as sa as a owner and under master database.
USE P001
DBCC SHRINKFILE (NextGen_Log, 51250)
GO

USE P002
DBCC SHRINKFILE (NextGen_Log, 51250)
GO

USE P003
DBCC SHRINKFILE (NextGen_Log, 51250)
GO

USE NGProd
DBCC SHRINKFILE (NextGen_Log, 51250)
GO

Or somebody have some different shrink log idea for weekly basis lt me know please.
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 380 total points
ID: 24412413
are you doing log backups?
0
 

Author Comment

by:aatishpatel
ID: 24412498
i am doing a full database backup every week and every other day a differential backup
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 380 total points
ID: 24412574
no log backups?
0
 

Author Comment

by:aatishpatel
ID: 24412691
but full should have that right?
  and one more thing the above steps had problem i did some modifications like just the size that you see is just assumable. but let me know if the rest is ok

USE P001
 
ALTER DATABASE [P001] SET RECOVERY SIMPLE
DBCC SHRINKFILE (NextGen_Log, 10240)
ALTER DATABASE [P001] SET RECOVERY FULL

GO

USE P002
ALTER DATABASE [P002] SET RECOVERY SIMPLE
DBCC SHRINKFILE (NextGen_Log, 10240)
ALTER DATABASE [P002] SET RECOVERY FULL
GO

USE P003
ALTER DATABASE [P003] SET RECOVERY SIMPLE
DBCC SHRINKFILE (NextGen_Log, 10240)
ALTER DATABASE [P003] SET RECOVERY FULL
GO

USE NGProd
ALTER DATABASE [NGProd] SET RECOVERY SIMPLE
DBCC SHRINKFILE (NextGen_Log, 10240)
ALTER DATABASE [NGProd] SET RECOVERY FULL
GO
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 380 total points
ID: 24412716
>>but full should have that right?

No, you obviously didn't read the articles I gave you.  Just set your databases to Simple (like you're doing in the script) and DO NOT move them back to FULL...there is no reason to.
0
 

Author Comment

by:aatishpatel
ID: 24412927
actually i have to make them full again, because if i had to do simple i would not do shrinkfile. and this is a production database and that is why full recovery is a must for that. now what do you suggest, are the scripts ok.
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 380 total points
ID: 24412967
the scripts are fine..but being ina  full recovery mode DOESN'T DO ANYTHING unless you're doing log backups....nothing whatsoever.  that is why I suggested you keep it in the simple mode.  The only advantage of FULL is that you can recover to a point in time...but you'll never be able to do that if you don't make log backups.
0
 

Author Comment

by:aatishpatel
ID: 24413251
but my manager said that every sunday evening just do shrinkfile. because on saturday we run full backup. that's all he said.
0
 

Author Closing Comment

by:aatishpatel
ID: 31582572
thanx all
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

828 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