Hi All,
I ran sp_spaceused and the result is following:
database_name database_size unallocated space
DB1 6462.50 MB 1466.20 MB
reserved data index_size unused
4108216 KB 1889512 KB 298952 KB 1919752 KB
sp_helpfile gives me the following:
name size maxsize growth
DB1_dat 5609600 KB Unlimited 10%
DB1_log 1008000 KB Unlimited 10%
I have run dbcc shrinkdb as well as dbcc shrinkfile but everytime I see
the same results.
I think the db size is much more than it is.
Could you please tell what the above results tell me and what to do?
Thanks a million in advance.
Best regards,
mamunHi
Probably you did not perfom BACKUP LOG for a while and your database is set
to FULL/BULK-LOGGED recovery mode .
Try to set recovery mode of the database to SIMPLE or BACKUP LOG and the
run DBCC SHRINKFILE .
For more details please refer to the BOL.
"microsoft.public.dotnet.languages.vb" <mamun_ah@.hotmail.com> wrote in
message news:1128825232.882522.295020@.g14g2000cwa.googlegroups.com...
> Hi All,
> I ran sp_spaceused and the result is following:
> database_name database_size unallocated space
> DB1 6462.50 MB 1466.20 MB
> reserved data index_size unused
> 4108216 KB 1889512 KB 298952 KB 1919752 KB
> sp_helpfile gives me the following:
> name size maxsize growth
> DB1_dat 5609600 KB Unlimited 10%
> DB1_log 1008000 KB Unlimited 10%
>
> I have run dbcc shrinkdb as well as dbcc shrinkfile but everytime I see
> the same results.
> I think the db size is much more than it is.
> Could you please tell what the above results tell me and what to do?
> Thanks a million in advance.
> Best regards,
> mamun
>|||The transaction log accumulates data until a backup of the database is
performed or the log is truncated.
You will also want to perform DBCC SHOWCONTIG to investigate the amount
of data/index fragmentation and DBCC INDEXDEFRAG to resolve it.
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"microsoft.public.dotnet.languages.vb" <mamun_ah@.hotmail.com> wrote in
message news:1128825232.882522.295020@.g14g2000cwa.googlegroups.com...
> Hi All,
> I ran sp_spaceused and the result is following:
> database_name database_size unallocated space
> DB1 6462.50 MB 1466.20 MB
> reserved data index_size unused
> 4108216 KB 1889512 KB 298952 KB 1919752 KB
> sp_helpfile gives me the following:
> name size maxsize growth
> DB1_dat 5609600 KB Unlimited 10%
> DB1_log 1008000 KB Unlimited 10%
>
> I have run dbcc shrinkdb as well as dbcc shrinkfile but everytime I see
> the same results.
> I think the db size is much more than it is.
> Could you please tell what the above results tell me and what to do?
> Thanks a million in advance.
> Best regards,
> mamun
>|||Just to avoid confusion:
> The transaction log accumulates data until a backup of the database is
performed or the log is
> truncated.
I'd like to phase above as:
The transaction log is truncated then a transaction log backup is performed
(or regularly when SQL
Server performs checkpoint if db in simple recovery mode). The log is not tr
uncated when a database
backup is performed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JT" <someone@.microsoft.com> wrote in message news:ufwTbnZzFHA.4032@.TK2MSFTNGP15.phx.gbl...
> The transaction log accumulates data until a backup of the database is
performed or the log is
> truncated.
> You will also want to perform DBCC SHOWCONTIG to investigate the amount
of data/index
> fragmentation and DBCC INDEXDEFRAG to resolve it.
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx[/ur
l]
> "microsoft.public.dotnet.languages.vb" <mamun_ah@.hotmail.com> wrote in mes
sage
> news:1128825232.882522.295020@.g14g2000cwa.googlegroups.com...
>|||Thanks a lot everyone for your suggestions. That helped.
Best regards,
mamun
Showing posts with label ran. Show all posts
Showing posts with label ran. Show all posts
Sunday, March 25, 2012
database size increased twice
We had to move exsiting database on new server and ran querey copying all re
cords into new table on new server.
System said that new table was created successfully and all records were cop
ied.
We decided to backup this new database, but it failed. Investigating of back
up problem led us to the point where we realised that our actuall database b
ecome 2 times larger - 150GB insted of 75 (original size).
Kind of lost where this additional chunk came from.
Will be thankful for any suggestions,
Alan.Alan,
Did you create new database with the same data device and log device as the
original one?
Did you try to shrink the data files using DBCC?
larry
"ALan" <anonymous@.discussions.microsoft.com> wrote in message
news:49C0FD90-FFFA-4CE5-B8F8-0A43E1E8B22D@.microsoft.com...
records into new table on new server.
copied.
backup problem led us to the point where we realised that our actuall
database become 2 times larger - 150GB insted of 75 (original size).
cords into new table on new server.
System said that new table was created successfully and all records were cop
ied.
We decided to backup this new database, but it failed. Investigating of back
up problem led us to the point where we realised that our actuall database b
ecome 2 times larger - 150GB insted of 75 (original size).
Kind of lost where this additional chunk came from.
Will be thankful for any suggestions,
Alan.Alan,
Did you create new database with the same data device and log device as the
original one?
Did you try to shrink the data files using DBCC?
larry
"ALan" <anonymous@.discussions.microsoft.com> wrote in message
news:49C0FD90-FFFA-4CE5-B8F8-0A43E1E8B22D@.microsoft.com...
quote:
> We had to move exsiting database on new server and ran querey copying all
records into new table on new server.
quote:
> System said that new table was created successfully and all records were
copied.
quote:
> We decided to backup this new database, but it failed. Investigating of
backup problem led us to the point where we realised that our actuall
database become 2 times larger - 150GB insted of 75 (original size).
quote:
> Kind of lost where this additional chunk came from.
> Will be thankful for any suggestions,
> Alan.
Sunday, March 11, 2012
Database Restores
I took some database backups from a client site to do some development.
There were 20G, 16G and 200MB
i ran it through EM but waited for like 20 minutes for the 20G and 16G but nothing happen. Went to Profiler -- there was no activity log for backup/restore events.
went to QA and tried to restore the backup -- with the stats option but still nothing happenned. The 200MB restore worked though. i have restored a databases over 20G but haven't really encountered this problem. Can anyone assist me ? or have come across this problem ?I have to bring 2 or 3 client databases in house a week. Our largest client db is only about 70 gb but here is my restore template. Never fails.
--STEP 1. Create the Database
CREATE DATABASE MyDatabase
--STEP 2. Get the physical file names
sp_helpdb MyDatabase
--STEP 3. Get the logical file names
RESTORE FILELISTONLY
FROM DISK = 'X:\MyBackup.BAK'
--STEP.4 RUN THE RECOVERY
ALTER DATABASE MyDatabase SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE MyDatabase
FROM DISK = 'X:\MyBackup.BAK'
WITH REPLACE,RECOVERY,STATS=10,
MOVE 'Logical_Data' TO 'D:\physical.mdf' ,
MOVE 'Logical_Log' TO 'E:\physiical.LDF'
GO
ALTER DATABASE MyDatabase SET MULTI_USER
GO
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
GO
USE MyDatabase
GO
DBCC SHRINKFILE(Logical_Log,1)
GO
USE MASTER
GO
ALTER DATABASE MyDatabase SET RECOVERY SIMPLE|||That's the sort of thing you should post for reference on sqlkit, Thrasy.|||I know. I am getting some stuff together. Sample code... I have an article started. I will probably start posting this weekend. I am looking forward to some biting criticism. Why don't you post your CSVtoINT thing and don't you have a first name \ last name from one field thing too. I just have a full version release coming plus customer projects by the end of the month and I am woefully behind.|||I have noticed a remarkable delay from issuing the restore command to the first status tick being reported. Esecially on large databases. It could be that SQL Server is verifying the backup before beginning the restore, but that would be a question for Microsoft.
There were 20G, 16G and 200MB
i ran it through EM but waited for like 20 minutes for the 20G and 16G but nothing happen. Went to Profiler -- there was no activity log for backup/restore events.
went to QA and tried to restore the backup -- with the stats option but still nothing happenned. The 200MB restore worked though. i have restored a databases over 20G but haven't really encountered this problem. Can anyone assist me ? or have come across this problem ?I have to bring 2 or 3 client databases in house a week. Our largest client db is only about 70 gb but here is my restore template. Never fails.
--STEP 1. Create the Database
CREATE DATABASE MyDatabase
--STEP 2. Get the physical file names
sp_helpdb MyDatabase
--STEP 3. Get the logical file names
RESTORE FILELISTONLY
FROM DISK = 'X:\MyBackup.BAK'
--STEP.4 RUN THE RECOVERY
ALTER DATABASE MyDatabase SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE MyDatabase
FROM DISK = 'X:\MyBackup.BAK'
WITH REPLACE,RECOVERY,STATS=10,
MOVE 'Logical_Data' TO 'D:\physical.mdf' ,
MOVE 'Logical_Log' TO 'E:\physiical.LDF'
GO
ALTER DATABASE MyDatabase SET MULTI_USER
GO
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
GO
USE MyDatabase
GO
DBCC SHRINKFILE(Logical_Log,1)
GO
USE MASTER
GO
ALTER DATABASE MyDatabase SET RECOVERY SIMPLE|||That's the sort of thing you should post for reference on sqlkit, Thrasy.|||I know. I am getting some stuff together. Sample code... I have an article started. I will probably start posting this weekend. I am looking forward to some biting criticism. Why don't you post your CSVtoINT thing and don't you have a first name \ last name from one field thing too. I just have a full version release coming plus customer projects by the end of the month and I am woefully behind.|||I have noticed a remarkable delay from issuing the restore command to the first status tick being reported. Esecially on large databases. It could be that SQL Server is verifying the backup before beginning the restore, but that would be a question for Microsoft.
Thursday, March 8, 2012
Database restore problem
A customer's Windows 2000 server would not reboot. They
ran a repair, but the drive letters on the server moved
ahead two spots. The drive letter D is now drive F.
I attempted a restore of a database through Enterprise
Manager, but I get the following error message :
"Device activation error: the physical file name d:\sql
db\otg.mdf: may be incorrect. File 'otg data' cannot be
restored to d:\sql db\otg.mdf'. Use with move to identify
a valid location for the file. Device activation error.
The physical file name 'D:\sql db\otglog.ldf may be
incorrect. File 'otglog' cannot be restored to 'd:\sql
db\otglog.ldf.' Use with move to identify a valid location
for the file. Restore db is terminating abnormally."
The original database files were located on C. A backup of
the database was saved to D.
What can I do to restore this database? Can anyone suggest
a query that restores the database to its previous state?
Thanks for your help.Hello Craig !
If you restore in EM you should change the path where you want to place the
new datafiles from the backup.
Please note, that the directories will NOT be created by EM, they must be
created before restoring the database.
HTH, Jens Süßmeyer.|||Try this:
RESTORE DATABASE otg
FROM DISK= 'c:\wherever the .bak file is'
WITH RECOVERY,replace,
MOVE 'otg_data' TO 'd:\sql_db\otg.mdf',
MOVE 'otgLog' TO 'd:\sql_db\otglog.ldf.'
Make sure you have enough permissions, with OTG imaging database you can use
the sysop user ;)
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Craig Kornacki" <ckornack@.localnet.com> wrote in message
news:034701c36f5b$2e13c320$a301280a@.phx.gbl...
> A customer's Windows 2000 server would not reboot. They
> ran a repair, but the drive letters on the server moved
> ahead two spots. The drive letter D is now drive F.
> I attempted a restore of a database through Enterprise
> Manager, but I get the following error message :
> "Device activation error: the physical file name d:\sql
> db\otg.mdf: may be incorrect. File 'otg data' cannot be
> restored to d:\sql db\otg.mdf'. Use with move to identify
> a valid location for the file. Device activation error.
> The physical file name 'D:\sql db\otglog.ldf may be
> incorrect. File 'otglog' cannot be restored to 'd:\sql
> db\otglog.ldf.' Use with move to identify a valid location
> for the file. Restore db is terminating abnormally."
> The original database files were located on C. A backup of
> the database was saved to D.
> What can I do to restore this database? Can anyone suggest
> a query that restores the database to its previous state?
> Thanks for your help.|||I know it's late, but what I would do is change the Drive letter to the
original drive letter using windows disk manager... This will likely prevent
other mapping kinds of errors from other applications as well.
"Craig Kornacki" <ckornack@.localnet.com> wrote in message
news:034701c36f5b$2e13c320$a301280a@.phx.gbl...
> A customer's Windows 2000 server would not reboot. They
> ran a repair, but the drive letters on the server moved
> ahead two spots. The drive letter D is now drive F.
> I attempted a restore of a database through Enterprise
> Manager, but I get the following error message :
> "Device activation error: the physical file name d:\sql
> db\otg.mdf: may be incorrect. File 'otg data' cannot be
> restored to d:\sql db\otg.mdf'. Use with move to identify
> a valid location for the file. Device activation error.
> The physical file name 'D:\sql db\otglog.ldf may be
> incorrect. File 'otglog' cannot be restored to 'd:\sql
> db\otglog.ldf.' Use with move to identify a valid location
> for the file. Restore db is terminating abnormally."
> The original database files were located on C. A backup of
> the database was saved to D.
> What can I do to restore this database? Can anyone suggest
> a query that restores the database to its previous state?
> Thanks for your help.|||Will this query work even if there is no D drive on the
server?
>--Original Message--
>Try this:
>RESTORE DATABASE otg
>FROM DISK= 'c:\wherever the .bak file is'
> WITH RECOVERY,replace,
> MOVE 'otg_data' TO 'd:\sql_db\otg.mdf',
> MOVE 'otgLog' TO 'd:\sql_db\otglog.ldf.'
>Make sure you have enough permissions, with OTG imaging
database you can use
>the sysop user ;)
>HTH
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"Craig Kornacki" <ckornack@.localnet.com> wrote in message
>news:034701c36f5b$2e13c320$a301280a@.phx.gbl...
>> A customer's Windows 2000 server would not reboot.
They
>> ran a repair, but the drive letters on the server moved
>> ahead two spots. The drive letter D is now drive F.
>> I attempted a restore of a database through Enterprise
>> Manager, but I get the following error message :
>> "Device activation error: the physical file name
d:\sql
>> db\otg.mdf: may be incorrect. File 'otg data' cannot
be
>> restored to d:\sql db\otg.mdf'. Use with move to
identify
>> a valid location for the file. Device activation
error.
>> The physical file name 'D:\sql db\otglog.ldf may be
>> incorrect. File 'otglog' cannot be restored to 'd:\sql
>> db\otglog.ldf.' Use with move to identify a valid
location
>> for the file. Restore db is terminating abnormally."
>> The original database files were located on C. A
backup of
>> the database was saved to D.
>> What can I do to restore this database? Can anyone
suggest
>> a query that restores the database to its previous
state?
>> Thanks for your help.
>
>.
>
ran a repair, but the drive letters on the server moved
ahead two spots. The drive letter D is now drive F.
I attempted a restore of a database through Enterprise
Manager, but I get the following error message :
"Device activation error: the physical file name d:\sql
db\otg.mdf: may be incorrect. File 'otg data' cannot be
restored to d:\sql db\otg.mdf'. Use with move to identify
a valid location for the file. Device activation error.
The physical file name 'D:\sql db\otglog.ldf may be
incorrect. File 'otglog' cannot be restored to 'd:\sql
db\otglog.ldf.' Use with move to identify a valid location
for the file. Restore db is terminating abnormally."
The original database files were located on C. A backup of
the database was saved to D.
What can I do to restore this database? Can anyone suggest
a query that restores the database to its previous state?
Thanks for your help.Hello Craig !
If you restore in EM you should change the path where you want to place the
new datafiles from the backup.
Please note, that the directories will NOT be created by EM, they must be
created before restoring the database.
HTH, Jens Süßmeyer.|||Try this:
RESTORE DATABASE otg
FROM DISK= 'c:\wherever the .bak file is'
WITH RECOVERY,replace,
MOVE 'otg_data' TO 'd:\sql_db\otg.mdf',
MOVE 'otgLog' TO 'd:\sql_db\otglog.ldf.'
Make sure you have enough permissions, with OTG imaging database you can use
the sysop user ;)
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Craig Kornacki" <ckornack@.localnet.com> wrote in message
news:034701c36f5b$2e13c320$a301280a@.phx.gbl...
> A customer's Windows 2000 server would not reboot. They
> ran a repair, but the drive letters on the server moved
> ahead two spots. The drive letter D is now drive F.
> I attempted a restore of a database through Enterprise
> Manager, but I get the following error message :
> "Device activation error: the physical file name d:\sql
> db\otg.mdf: may be incorrect. File 'otg data' cannot be
> restored to d:\sql db\otg.mdf'. Use with move to identify
> a valid location for the file. Device activation error.
> The physical file name 'D:\sql db\otglog.ldf may be
> incorrect. File 'otglog' cannot be restored to 'd:\sql
> db\otglog.ldf.' Use with move to identify a valid location
> for the file. Restore db is terminating abnormally."
> The original database files were located on C. A backup of
> the database was saved to D.
> What can I do to restore this database? Can anyone suggest
> a query that restores the database to its previous state?
> Thanks for your help.|||I know it's late, but what I would do is change the Drive letter to the
original drive letter using windows disk manager... This will likely prevent
other mapping kinds of errors from other applications as well.
"Craig Kornacki" <ckornack@.localnet.com> wrote in message
news:034701c36f5b$2e13c320$a301280a@.phx.gbl...
> A customer's Windows 2000 server would not reboot. They
> ran a repair, but the drive letters on the server moved
> ahead two spots. The drive letter D is now drive F.
> I attempted a restore of a database through Enterprise
> Manager, but I get the following error message :
> "Device activation error: the physical file name d:\sql
> db\otg.mdf: may be incorrect. File 'otg data' cannot be
> restored to d:\sql db\otg.mdf'. Use with move to identify
> a valid location for the file. Device activation error.
> The physical file name 'D:\sql db\otglog.ldf may be
> incorrect. File 'otglog' cannot be restored to 'd:\sql
> db\otglog.ldf.' Use with move to identify a valid location
> for the file. Restore db is terminating abnormally."
> The original database files were located on C. A backup of
> the database was saved to D.
> What can I do to restore this database? Can anyone suggest
> a query that restores the database to its previous state?
> Thanks for your help.|||Will this query work even if there is no D drive on the
server?
>--Original Message--
>Try this:
>RESTORE DATABASE otg
>FROM DISK= 'c:\wherever the .bak file is'
> WITH RECOVERY,replace,
> MOVE 'otg_data' TO 'd:\sql_db\otg.mdf',
> MOVE 'otgLog' TO 'd:\sql_db\otglog.ldf.'
>Make sure you have enough permissions, with OTG imaging
database you can use
>the sysop user ;)
>HTH
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"Craig Kornacki" <ckornack@.localnet.com> wrote in message
>news:034701c36f5b$2e13c320$a301280a@.phx.gbl...
>> A customer's Windows 2000 server would not reboot.
They
>> ran a repair, but the drive letters on the server moved
>> ahead two spots. The drive letter D is now drive F.
>> I attempted a restore of a database through Enterprise
>> Manager, but I get the following error message :
>> "Device activation error: the physical file name
d:\sql
>> db\otg.mdf: may be incorrect. File 'otg data' cannot
be
>> restored to d:\sql db\otg.mdf'. Use with move to
identify
>> a valid location for the file. Device activation
error.
>> The physical file name 'D:\sql db\otglog.ldf may be
>> incorrect. File 'otglog' cannot be restored to 'd:\sql
>> db\otglog.ldf.' Use with move to identify a valid
location
>> for the file. Restore db is terminating abnormally."
>> The original database files were located on C. A
backup of
>> the database was saved to D.
>> What can I do to restore this database? Can anyone
suggest
>> a query that restores the database to its previous
state?
>> Thanks for your help.
>
>.
>
Saturday, February 25, 2012
database repair without data loss?
Scenario: Database maintenance plan failed in "check data and index linkage" activity. Ran DBCC CHECKDB WITH PHYSICAL_ONLY option which revealed a few "page id" problems. It appears all errors on related to one table. The CHECKDB stated specifically: "repair_allow_data_loss is the minimum repair level for the errors found"
My question is: Is there any way to repair database/table without data loss?Yes, restore from your last backup and apply all the log backups since the
backup was taken (stopping at the point the corruption appears if necessary)
It is not *guaranteed* that repair will have to delete data to repair the
database but it is highly likely (if REPAIR_ALLOW_DATA_LOSS is needed).
Repair should always be your last resort. You should also determine the root
cause of the corruption (i.e. examine NT event logs, SQL Server error log,
run hardware diagnostics etc) as a hardware fault will most likely cause the
same or similar corruption in future if not corrected.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alan T" <infopro@.3wlogic.net> wrote in message
news:FD16E2B3-DEF4-486C-8A80-DFABB549720B@.microsoft.com...
> Scenario: Database maintenance plan failed in "check data and index
linkage" activity. Ran DBCC CHECKDB WITH PHYSICAL_ONLY option which
revealed a few "page id" problems. It appears all errors on related to one
table. The CHECKDB stated specifically: "repair_allow_data_loss is the
minimum repair level for the errors found".
> My question is: Is there any way to repair database/table without data
loss?|||Thanks, Paul. With the help of someone with a great deal more experience I was able to recover virtually all data.
The corruption was limited to one table, so after some minor unsuccessful attempts at repair we ran DBCC CHECKTABLE WITH REPAIR_ALLOW_DATA_LOSS. We then restored a "good" backup into a temporary database and from that database pulled records from the problem table that were missing in the production table after the REPAIR_ALLOW_DATA_LOSS. It appears we were able to recover all but about 11 records. It's wasn't a "perfect" recovery but I'm happy and grateful for the help.
Best wishes.
My question is: Is there any way to repair database/table without data loss?Yes, restore from your last backup and apply all the log backups since the
backup was taken (stopping at the point the corruption appears if necessary)
It is not *guaranteed* that repair will have to delete data to repair the
database but it is highly likely (if REPAIR_ALLOW_DATA_LOSS is needed).
Repair should always be your last resort. You should also determine the root
cause of the corruption (i.e. examine NT event logs, SQL Server error log,
run hardware diagnostics etc) as a hardware fault will most likely cause the
same or similar corruption in future if not corrected.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alan T" <infopro@.3wlogic.net> wrote in message
news:FD16E2B3-DEF4-486C-8A80-DFABB549720B@.microsoft.com...
> Scenario: Database maintenance plan failed in "check data and index
linkage" activity. Ran DBCC CHECKDB WITH PHYSICAL_ONLY option which
revealed a few "page id" problems. It appears all errors on related to one
table. The CHECKDB stated specifically: "repair_allow_data_loss is the
minimum repair level for the errors found".
> My question is: Is there any way to repair database/table without data
loss?|||Thanks, Paul. With the help of someone with a great deal more experience I was able to recover virtually all data.
The corruption was limited to one table, so after some minor unsuccessful attempts at repair we ran DBCC CHECKTABLE WITH REPAIR_ALLOW_DATA_LOSS. We then restored a "good" backup into a temporary database and from that database pulled records from the problem table that were missing in the production table after the REPAIR_ALLOW_DATA_LOSS. It appears we were able to recover all but about 11 records. It's wasn't a "perfect" recovery but I'm happy and grateful for the help.
Best wishes.
Subscribe to:
Posts (Atom)