Shrinking the log file does not reduce size

Question

Shrinking the log file does not reduce size

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

What version (edition) of SQL Server ar you currently using?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

IT researcher (5/3/2013)
Whether any other tweaks available which can help me to remove replication related data from it?

It's not that there's replicated data, there's replication markers left in the log. There are no 'tweaks' that fix that. You need to persuade SQL to clear that out and since it thinks that the DB's not replicated about the only way is to create replication first.

Got a dev edition that you can move the DB to for fixing?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Lynn Pettis (5/3/2013)
What version (edition) of SQL Server ar you currently using?

Express. Was stated earlier.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

GilaMonster (5/3/2013)
Lynn Pettis (5/3/2013)
What version (edition) of SQL Server ar you currently using?
Express. Was stated earlier.

Thank you. I looked earlier and just could not seem to find it. Just another snake in the grass.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Your database isn't really that big compared to others. Another choice you could make if you have the maintenace window do it is this:

1. Script the database (all of it)

2. Create a new empty database using the scripts from above

3. Transfer the data from the old database to the new database

4. Drop the old database (or rename it)

5. Rename the new database to the old datbase name.

You can test this multiple times leaving off steps 4 and 5 until you feel confortable with what needs to be done.

IT researcher SSCertifiable Points: 7567 More actions · Answer 6

Lynn Pettis (5/3/2013)
Your database isn't really that big compared to others. Another choice you could make if you have the maintenace window do it is this:
1. Script the database (all of it)
2. Create a new empty database using the scripts from above
3. Transfer the data from the old database to the new database
4. Drop the old database (or rename it)
5. Rename the new database to the old datbase name.
You can test this multiple times leaving off steps 4 and 5 until you feel confortable with what needs to be done.

I imported the database as suggested by Lynn Pettis

After importing i ran query to check log wait

select log_reuse_wait_desc from sys.databases where name = 'DBName'

Output was 'NOTHING'(earlier output was 'replication').

Now i tried to shrink the database, but again shrink didn't reduce log file size,i also tried shrinking log file by taking log backup etc etc.

But nothing worked.Still log file remains 7 gb.

If i try to shrink the log file by using SSMS(Right clcik db->task->shrink->files) and then select the filetype as "log" then it shows Currently allocated space is :7498.19 MB and Available free space:7063.63 MB (94%) .Then i selected 'release unused space' and ok. But still log file didnt reduced a little also!!

I also checked by running following

Execute use <databasename> dbcc loginfo

But most of them have status 0. so log is not in use.But shrinks gets failed to reduce size.So how can i reduce it? How to troubleshoot