[Solved] .ldf = 82 gigs & .mdf = 3.8 gigs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

[Solved] .ldf = 82 gigs & .mdf = 3.8 gigs

Author

Topic

jbruyet1
Starting Member

35 Posts

Posted - 2014-05-07 : 16:52:57

Hey all, I just inherited a SQL server even though I don't have any SQL training or experience. I was working with a vendor (they don't provide any SQL services) and learned that I have 82.7 gigs of SQL Server Database Transaction Log Files. I also learned that I have 3.8 gigs of SQL Server Database Primary Data Files. That sounds to me like something is seriously out of balance. I Googled this issue thinking I could just delete old files and now I think both sets of files are connected. AND, that there are pitfalls waiting for someone who just inherited a SQL server and doesn't know the terrain.

So, does this mean I have some serious SQL issues? Is there a simple way to correct this problem?

Thanks,

Joe B

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-07 : 17:14:47

If your data is important to your company you should recommend getting a DBA in immediately (either as a full-time hire or contractor) just to get you squared away.

If that is not possible for some reason:

Most likely scenario is that the database is in FULL recovery mode but there is no process in place to take full backups. In that case the t-log will continue to grow indefinitely. The solution would be to either put the database in SIMPLE recovery mode which should shrink your t-log. Then put it back in FULL and incorporate some sort of maintenance backup plan. Or leave the recovery mode in FULL and do a full backup. Then get the maintenance plan set.

Books Online will be your friend:
Shrinking the transaction log

Be One with the Optimizer
TG

jbruyet1
Starting Member

35 Posts

Posted - 2014-05-07 : 18:30:51

Hi TG, I just found the Microsoft SQL Server Management Studio. I went through and did a full backup on the four largest databases but that didn't have any effect on their transaction logs. I'm going to go through now and change their recovery mode to simple and then change it back again.

I believe I now have a full backup scheduled. The backup files should go to a sub-folder in a folder that is getting backed up to tape. I will report back with my results.

Thanks for the help,

Joe B

jbruyet1
Starting Member

35 Posts

Posted - 2014-05-07 : 18:40:35

Or not. I just right-clicked on the first database in the list and this error message popped up:

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

Cannot show requested dialog.

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

Cannot show requested dialog. (SqlMgmt)

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

Property Owner is not available for Database '[FNAD_SQL]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.5000.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=Owner&LinkId=20476

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

OK
------------------------------

Is there someplace in the Management Studio to add users to a SQL Administrator group? If so I'm not finding it.

Thanks,

Joe B

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-07 : 18:44:50

Backups do not impact the size of the ldf file. BACKUP LOG affects what is inside the ldf file. A shrink operation affects the size of the files.

I'm not familiar with that error. Have you been given sysadmin?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

jbruyet1
Starting Member

35 Posts

Posted - 2014-05-07 : 19:12:51

Hi tkizer, I haven't been given anything except the box. I just checked and I see I didn't add what might be an important piece of information -- it's SQL Server 2005.

I've tried shrinking the log files from within the Management Studio but that had little effect on the files.

In Windows server there's a way to take ownership of files and folders. Is there something like that in SQL Server so I can get into the properties page?

Thanks,

Joe B

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-08 : 11:53:14

In Management Studio, open up Security then Logins. Find your login and then open Properties. Go to Server Roles. Is sysadmin checked? If you are unable to find your login, you've been granted access through a group and will need to determine which and then do the same thing to check if you have sysadmin.

If shrinking had little effect, then you likely are do not have transaction log backups in place for a database using FULL or BULK_LOGGED recovery models.

We need to work out what permissions you have first. If you do have sysadmin, then the error you are encountering is likely just a UI bug. If that's the case, we can run commands instead of using the UI. It'll be a little challenging for someone without DBA experience, but we'll walk you through it. To fix a UI bug, you could try installing SQL Server 2005 service pack 4 and then the latest cumulative update package. You would do this on the machine where you are using Management Studio. Service packs and CUs are for the database engine as well as the client tools.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

jbruyet1
Starting Member

35 Posts

Posted - 2014-05-08 : 12:54:04

I added my name to the Logins folder and I now have public and sysadmin checked. I've restarted Management Studio but I'm still unable to get to Properties on the databases. Do I need to do a server reboot? If so I'll have to wait until tonight to do it.

Thanks,

Joe B

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-08 : 13:47:29

Please do not reboot it. This is a client tool issue and not an issue with SQL Server.

Run this in a query window:

exec sp_helpdb 'YourDatabaseNameGoesHere' --the database with the huge ldf file

Show us the output, making sure to show us the entire status column.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

jbruyet1
Starting Member

35 Posts

Posted - 2014-05-08 : 14:33:29

Is there a way to pop up a quick query window and run a query from there? I went in and created a query and ran it by pressing F-5. Wow, now I understand your comment about the status column:

Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled

Here's the rest:

Name		db_size		owner	dbid	created			compatibility level
FNPR_SQL	31946.38 MB	NULL	16		Nov 13 2009		90

Thanks,

Joe B

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-08 : 14:39:20

I don't understand your question as what you did was run a query.

This database is using FULL recovery model, which means you need to have transaction log backups in place to manage it. Are you able to check the SQL Agent jobs? Or even the SQL Server Error Log as that will show successful/failed backups by default (those messages can be turned off though).

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

jbruyet1
Starting Member

35 Posts

Posted - 2014-05-08 : 16:51:19

Hi tkizer, I guess my question regrading queries was a little vague. I was looking for a window to open where I'd type something, hit enter and the query would run. I had to do quite a bit of searching after creating the query job before I found that I could just hit F5 and the query would run.

I just tried to create a backup job for the transaction logs but I couldn't find anything in the Maintenance Plan Tasks that said anything about backing up those logs. Where would I start the process of backing up the logs? Is it something I can figure out just by doing a little spelunking?

Thanks,

Joe B

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-08 : 17:04:12

For the query question, in addition to F5, you can click the Execute button in the toolbar.

I don't use maintenance plans, but the option is definitely in there to backup transaction logs.

Since log backups are not setup, we need to determine if you are using the proper recovery model. Should a hardware failure, accidental deletion of data, disaster, etc occur, how much data can you lose? If you do daily full backups, would restoring to the last full backup be sufficient? If it is, then you can switch your recovery model to SIMPLE, which would mean no log backups. If the business requires less data loss, then keep it at FULL and implement the log backups. A pretty common standard is to back them up every 15 minutes.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-08 : 17:06:08

Once log backups are running successfully OR the recovery model is switched to SIMPLE, you should be able to shrink the file down. I'd probably shrink it down to 1GB given the size of your mdf file, but that's not based on knowing your system. You'd have to monitor the file over time to determine what size it really needs. It's dependent upon your largest transaction and how often log backups are running (if not using SIMPLE). Index rebuilds, which are an example of a large transaction, can significantly increase the ldf size, but that depends on the index sizes.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

jbruyet1
Starting Member

35 Posts

Posted - 2014-05-09 : 14:32:46

Ok, I didn't have the disk space on the server to back up the transaction logs so I'm backing them up to my Snap Server (UNC path) at this time. I'm really hoping this will drop my 82 gigs of transaction logs to something quite a bit less.

I'm still unable to change the Backup Mode from Full to Simple due to my being blocked by the "Property Owner is not available for Database" error. Any ideas on that end?

Thanks,

Joe B

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-09 : 14:51:25

quote:
Originally posted by jbruyet1
I'm still unable to change the Backup Mode from Full to Simple due to my being blocked by the "Property Owner is not available for Database" error. Any ideas on that end?

try executing this:
(you need to change the @login value to a valid login with sql admin privs. Or if you know the 'sa' password set it to 'sa')


exec sp_changedbowner @login = '<yourAdinistratorLogin>'

Be One with the Optimizer
TG

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-09 : 16:10:44

quote:
Originally posted by jbruyet1

Ok, I didn't have the disk space on the server to back up the transaction logs so I'm backing them up to my Snap Server (UNC path) at this time. I'm really hoping this will drop my 82 gigs of transaction logs to something quite a bit less.

I'm still unable to change the Backup Mode from Full to Simple due to my being blocked by the "Property Owner is not available for Database" error.

After the log backup completes, you'll need to shrink it in order for the file to shrink. DBCC SHRINKFILE

If you plan on changing the recovery model to SIMPLE, then there is no need to backup the log. After making the change (ALTER DATABASE), shrink the file with DBCC SHRINKFILE.

Using SIMPLE recovery model means you won't be able to restore to a point-in-time should the need arise. You are limited to your full backups (and differentials if you have them). With tlog backups, you can restore to a point in time, but you can't backup a tlog when using SIMPLE recovery model.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

jbruyet1
Starting Member

35 Posts

Posted - 2014-05-09 : 18:49:51

Hi TG, I tried logging in with the three accounts that have sa checked in Server Roles and none of them allow me access to the Properties of the databases. I don't know the password for the sa account and I was hesitant to change the password to something else in case some process or service is running under it.

The backup of the Transaction Logs finished successfully. There's a positive thing.

Thanks,

Joe B

jbruyet1
Starting Member

35 Posts

Posted - 2014-05-09 : 18:56:21

Hi tkizer, sorry I missed your post. I'll Google DBCC SHRINKFILE and see how to do that. I will probably also change to a simple backup because we've always thought that if anything went wrong we'd have to go back to yesterday's end-of-day data. Later on I might change it but for now I'm just trying to recover some hard disk real estate -- I'm down to 16 gigs free.

Thanks,

Joe B

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-11 : 22:18:17

Because the result of your sp_helpdb showed a NULL for owner I believe you just need to run the command I suggested above. My only point was to use a valid login with admin privs as the @login value. Once you change the the database owner to the valid login you should be able to view the properties.

Be One with the Optimizer
TG

jbruyet1
Starting Member

35 Posts

Posted - 2014-05-12 : 13:39:43

I think I need to find a different window to enter this command. I tried using the SQL query window because it's the only window I can find but entering this command:

exec sp_changedbowner @sa = LINK\Administrator

gives me this error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '\'.

I did some Googling and saw that some commands can be entered through the command prompt but I'm not sure which "instance" I'm supposed to connect to.

Thanks,

Joe B

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources