Solved

sql server scripts needed

Posted on 2009-05-18
15
295 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
Comment Utility
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
Comment Utility
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
Comment Utility
one of the same links I posted for you before....

http://blogs.techrepublic.com.com/datacenter/?p=448
0
 
LVL 6

Assisted Solution

by:tangchunfeng
tangchunfeng earned 120 total points
Comment Utility
0
 

Author Comment

by:aatishpatel
Comment Utility
ok thanx
0
 

Author Comment

by:aatishpatel
Comment Utility
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
Comment Utility
are you doing log backups?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:aatishpatel
Comment Utility
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
Comment Utility
no log backups?
0
 

Author Comment

by:aatishpatel
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
thanx all
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now