DBCC CHECKDB: bak

Showing posts with label bak. Show all posts

Thursday, March 22, 2012

DB updates when limited to bak/trn files only

Hi,
I couldn't make a clear title without writing 2 lines.

My problem (I tried to find out through the archives):
this scenario will be used for several DBs on severals servers.

The remotes servers are not mine, I have only access to the backups files, I have no rights to setup a replication relationship.

I'm using a repository server with SQL 2005, and daily, I need to get the latest Full backup from an SQL2000 server, copy it to the repository server, restore it then delete the .bak file.
This is possible by using many different scripts (like .vbs to copy and rename the latest Full backups) then I use SQL job for daily restore.
Process is too long and the time estimated to start next task (and the backup keep growing).

Is there a way to do everything via SQL2005 script (job)?

Initially, the problem is that I have to do this with the Full backups every days (around 5 Dbs 8 Go in average). So if I can use the latest transaction log files (that would eliminate my first question).
The best way is to use log shipping as well but sql2005 is needed on both sides.

Hope I'm clear.

Thanks for any help!Do you need the databases in read only mode, or fully recovered?

It would be difficult to use the tran log backups, because you could never recover the database.

However, saying that, I wonder if you could keep the receiving database in an unrecovered Read-Only mode, and transactionally publish that database to a third database that would be recovered?

Source Receiving Recovered
Server Full Backup Server DB Server
[----] ------> [----] [----]
[----] Tran Log backups [----] Transactional [----]
[----] ------> [----] Replication [----]
[----] [----] ------> [----]
[----] [----] [----]|||Database in read only mode should be enough as they are used for extraction.
The best and simpler solution again is having the rights on the remotes server but I need to do with it.

So as I won't be able to restore the DB with Trn log only, I still need to copy the Full Bak. I'm fine with that as having the full from 12 h ago is fine.
I guess the intermediary server solution in not needed for that. I know this is a widely used solution but maybe not for what I need (until SQL 2005 migration on remotes servers).

So now, saying I'll only use the .bak, is here a way I can automate from my local server the copy and restore?

Backups are done on same Remote servers, then picked up from there.

Thanks|||So now, saying I'll only use the .bak, is here a way I can automate from my local server the copy and restore?

Since I don't know all the info regarding your provider as regards backup retention period, etc ... here is an overview:

1. Download robocopy - it's free and very good - will auto-restart a file copy if interrupted

2. create a sql script that will be executed by sqlcmd (command line interface) - script builds the filename to download (assumes provider is building filenames including date of backup in filename) and uses robocopy to perform the copy to your local storage. Also have that script create a small file called "CopyComplete.txt" after robocopy successfully finishes (you could copy it from another locatiopn"

3. Create a scheduled job that checks periodically for the "CopyComplete.txt" file. When it finds it, it deletes it and then does the database restore using the replace option.

This is one method ... there are many variations of this ... you chose one and debug it until it works, then use that as a template for all your databases.|||Bkp retention is 2 days (2 full and 6 trn) (so need to grag the last ones, always same time)

I'll try Robocopy with your steps then and will let u know how i is.

thanks again

Monday, March 19, 2012

DB restore error

Hi
I'm passing the following SQL statement to my SQLServer:
RESTORE DATABASE myDB FROM DISK 'd:\mssql\backup2\myDB.bak' WITH MOVE 'myDB'
TO 'd:\mssql\data\myDB.mdf', MOVE 'myDB_log' TO
'd:\mssql\data\myDB_log.ldf'
However, when I run this, I get the following error:
Server: Msg 3234, Level 16, State2, Line 1
Logical file 'myDB' is not part of database 'myDB'. Use RESTORE
FILELISTONLY to list the logical file names.
I've had a look in the help and I'm not really any the wiser. Can someone
explain to me what is occuring here?
Thanks
GriffOkay - understood it now...the "data" was called "myDB_Data" and not "myDB".
Griff|||Hi
Looks like you specify incorrect path to your log file or you don't have
the path created on hard disk.
Did you run FILELISTONLY as it is suggested ?
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:exzIowrzDHA.1576@.TK2MSFTNGP11.phx.gbl...
> Hi
> I'm passing the following SQL statement to my SQLServer:
> RESTORE DATABASE myDB FROM DISK 'd:\mssql\backup2\myDB.bak' WITH MOVE
'myDB'
> TO 'd:\mssql\data\myDB.mdf', MOVE 'myDB_log' TO
> 'd:\mssql\data\myDB_log.ldf'
> However, when I run this, I get the following error:
> Server: Msg 3234, Level 16, State2, Line 1
> Logical file 'myDB' is not part of database 'myDB'. Use RESTORE
> FILELISTONLY to list the logical file names.
> I've had a look in the help and I'm not really any the wiser. Can someone
> explain to me what is occuring here?
> Thanks
> Griff
>

DB Restore

HI, I am restoring a DB .BAK file which is around 1.8 GBs. It is taking a lot of time to complete. Is this expected?

Thanks a lot.

It all depends on your hardware components and system configuration.......generally speaking it should not take more than 10 mins for 2 GB backup files..........also check if the backup file is valid by using,

restore verifyonly from disk='path of the backup file'

Also I think you should have posted this in database engine forum as that seems to be more appropriate for your question......

|||

Also, it may be that your data and log files are much larger than 1.8GB and its taking a long time to create these files.

The database backup file will be the size of the actual data stored whereas the files you restore will have the blank pages allocated as well which could make them much larger.

This is quite a common scenario with the transaction log when it can grow quite large over time but is never physically truncated.

HTH!

DB Restore

I often have to restore databases from backups sent to me by clients.
I do a
RESTORE FILELISTONLY FROM DISK='c:\db.bak'
which gives me the data and log file names, say 'data_file' and
'data_file_log'
and then a
RESTORE DATABASE DbName
FROM DISK = 'c:\db.bak'
WITH MOVE 'data_file' TO 'c:\test\db.mdf',
MOVE 'data_file_log' TO 'c:\test\db.ldf'
Does anyone know a way that I can read the names of the data and log files
into variables
in the 'RESTORE FILELISTONLY' command and then substitute them
into the 'RESTORE DATABASE' command ?
TIA
SteveYou should be able to do like below:
CREATE TABLE #FileDetails(...)
INSERT #FileDetails(col1, col2...)
EXEC('RESTORE FILELISTDETAILS...')
Now you can read the stuff off of that table and get the stuff into TSQL
variables. And then use those variables in the RESTORE command. In case SQL
Server doesn't accepts variables in RESTORE for the relevant option, you can
use dynamic SQL to execute the RESTORE command.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Steve W" <lsl@.btconnect.com.no_spam> wrote in message
news:OZXew4N7DHA.2404@.TK2MSFTNGP12.phx.gbl...
> I often have to restore databases from backups sent to me by clients.
> I do a
> RESTORE FILELISTONLY FROM DISK='c:\db.bak'
> which gives me the data and log file names, say 'data_file' and
> 'data_file_log'
> and then a
> RESTORE DATABASE DbName
> FROM DISK = 'c:\db.bak'
> WITH MOVE 'data_file' TO 'c:\test\db.mdf',
> MOVE 'data_file_log' TO 'c:\test\db.ldf'
> Does anyone know a way that I can read the names of the data and log files
> into variables
> in the 'RESTORE FILELISTONLY' command and then substitute them
> into the 'RESTORE DATABASE' command ?
> TIA
> Steve
>

DB Restore

I often have to restore databases from backups sent to me by clients.
I do a
RESTORE FILELISTONLY FROM DISK='c:\db.bak'
which gives me the data and log file names, say 'data_file' and
'data_file_log'
and then a
RESTORE DATABASE DbName
FROM DISK = 'c:\db.bak'
WITH MOVE 'data_file' TO 'c:\test\db.mdf',
MOVE 'data_file_log' TO 'c:\test\db.ldf'
Does anyone know a way that I can read the names of the data and log files
into variables
in the 'RESTORE FILELISTONLY' command and then substitute them
into the 'RESTORE DATABASE' command ?
TIA
SteveYou should be able to do like below:
CREATE TABLE #FileDetails(...)
INSERT #FileDetails(col1, col2...)
EXEC('RESTORE FILELISTDETAILS...')
Now you can read the stuff off of that table and get the stuff into TSQL
variables. And then use those variables in the RESTORE command. In case SQL
Server doesn't accepts variables in RESTORE for the relevant option, you can
use dynamic SQL to execute the RESTORE command.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Steve W" <lsl@.btconnect.com.no_spam> wrote in message
news:OZXew4N7DHA.2404@.TK2MSFTNGP12.phx.gbl...
> I often have to restore databases from backups sent to me by clients.
> I do a
> RESTORE FILELISTONLY FROM DISK='c:\db.bak'
> which gives me the data and log file names, say 'data_file' and
> 'data_file_log'
> and then a
> RESTORE DATABASE DbName
> FROM DISK = 'c:\db.bak'
> WITH MOVE 'data_file' TO 'c:\test\db.mdf',
> MOVE 'data_file_log' TO 'c:\test\db.ldf'
> Does anyone know a way that I can read the names of the data and log files
> into variables
> in the 'RESTORE FILELISTONLY' command and then substitute them
> into the 'RESTORE DATABASE' command ?
> TIA
> Steve
>

Saturday, February 25, 2012

DB Maintenance Plan not working properly

In my maintenance plan, i have specified that it should delete bak files
older than 3 days and trn files also older than 3 days. It is surprising
doing that for trn files but the bak are not being deleted. I have 10 small
databases and one large database. It is accumulating the bak files longer
than 3 days while its doing the trn properly. What could cause this to happen
as it is filling up my hardisk. Due to this my large database cannot get
backed up as its saying no hard disk space. Please let me know where the
problem is. thanks in advance.
Below KB might help:
http://support.microsoft.com/default...&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"zj" <zj@.discussions.microsoft.com> wrote in message
news:BF147E4E-EEA3-49CD-83BB-B533FC772F49@.microsoft.com...
> In my maintenance plan, i have specified that it should delete bak files
> older than 3 days and trn files also older than 3 days. It is surprising
> doing that for trn files but the bak are not being deleted. I have 10 small
> databases and one large database. It is accumulating the bak files longer
> than 3 days while its doing the trn properly. What could cause this to happen
> as it is filling up my hardisk. Due to this my large database cannot get
> backed up as its saying no hard disk space. Please let me know where the
> problem is. thanks in advance.

DB Maintenance Plan not working properly

In my maintenance plan, i have specified that it should delete bak files
older than 3 days and trn files also older than 3 days. It is surprising
doing that for trn files but the bak are not being deleted. I have 10 small
databases and one large database. It is accumulating the bak files longer
than 3 days while its doing the trn properly. What could cause this to happen
as it is filling up my hardisk. Due to this my large database cannot get
backed up as its saying no hard disk space. Please let me know where the
problem is. thanks in advance.Below KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"zj" <zj@.discussions.microsoft.com> wrote in message
news:BF147E4E-EEA3-49CD-83BB-B533FC772F49@.microsoft.com...
> In my maintenance plan, i have specified that it should delete bak files
> older than 3 days and trn files also older than 3 days. It is surprising
> doing that for trn files but the bak are not being deleted. I have 10 small
> databases and one large database. It is accumulating the bak files longer
> than 3 days while its doing the trn properly. What could cause this to happen
> as it is filling up my hardisk. Due to this my large database cannot get
> backed up as its saying no hard disk space. Please let me know where the
> problem is. thanks in advance.

DB Maintenance Plan not working properly

In my maintenance plan, i have specified that it should delete bak files
older than 3 days and trn files also older than 3 days. It is surprising
doing that for trn files but the bak are not being deleted. I have 10 small
databases and one large database. It is accumulating the bak files longer
than 3 days while its doing the trn properly. What could cause this to happe
n
as it is filling up my hardisk. Due to this my large database cannot get
backed up as its saying no hard disk space. Please let me know where the
problem is. thanks in advance.Below KB might help:
http://support.microsoft.com/defaul...2&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"zj" <zj@.discussions.microsoft.com> wrote in message
news:BF147E4E-EEA3-49CD-83BB-B533FC772F49@.microsoft.com...
> In my maintenance plan, i have specified that it should delete bak files
> older than 3 days and trn files also older than 3 days. It is surprising
> doing that for trn files but the bak are not being deleted. I have 10 smal
l
> databases and one large database. It is accumulating the bak files longer
> than 3 days while its doing the trn properly. What could cause this to hap
pen
> as it is filling up my hardisk. Due to this my large database cannot get
> backed up as its saying no hard disk space. Please let me know where the
> problem is. thanks in advance.

Friday, February 17, 2012

DB is boated when restored

I have a *.bak file that is 1.7GB in size. When I do a restore the log file
is 3.0Gb and the data file is 16GB. Does anyone have any idea on what could
be causing this?
This is related to the other newsgroup post of "SQL 2005 db won't shrink".
Also, this db is a production Dynamics AX 4.0.
Any help/direction/info would be greatly appreciated.A database backup file only includes the data used in the database files and also the log records
produced while performing the backup. That backup was performed on a database which had free space
in both database file and the log file. When you restore the backup, SQL Server has to create the
database with the same file size as all the database files had when the backup was performed. So,
what you see is perfectly normal.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Patrick R." <PatrickR@.discussions.microsoft.com> wrote in message
news:7429D298-69ED-444B-87E1-0659CF2F74B9@.microsoft.com...
>I have a *.bak file that is 1.7GB in size. When I do a restore the log file
> is 3.0Gb and the data file is 16GB. Does anyone have any idea on what could
> be causing this?
> This is related to the other newsgroup post of "SQL 2005 db won't shrink".
> Also, this db is a production Dynamics AX 4.0.
> Any help/direction/info would be greatly appreciated.|||Patrick R. wrote:
> I have a *.bak file that is 1.7GB in size. When I do a restore the log file
> is 3.0Gb and the data file is 16GB. Does anyone have any idea on what could
> be causing this?
> This is related to the other newsgroup post of "SQL 2005 db won't shrink".
> Also, this db is a production Dynamics AX 4.0.
> Any help/direction/info would be greatly appreciated.
>
I didn't happen to read your other reference topic.
Have you looked at the dbcc shrinkdatabase with the truncate only option
TRUNCATEONLY
Releases all free space at the end of the file to the operating
system but does not perform any page movement inside the file. The
data file is shrunk only to the last allocated extent.
/target_percent/ is ignored if specified with TRUNCATEONLY.|||A misunderstanding, so let me clarify...I need to remove all the "space
available" from this db, no matter how it gets there. And I've tried all the
shrinkdatabase, shrinkfiles, put in simple recover mode, backed up, restored,
etc. And the space is still there. So what can remove the unwanted space?
Thanks
"Tibor Karaszi" wrote:
> A database backup file only includes the data used in the database files and also the log records
> produced while performing the backup. That backup was performed on a database which had free space
> in both database file and the log file. When you restore the backup, SQL Server has to create the
> database with the same file size as all the database files had when the backup was performed. So,
> what you see is perfectly normal.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Patrick R." <PatrickR@.discussions.microsoft.com> wrote in message
> news:7429D298-69ED-444B-87E1-0659CF2F74B9@.microsoft.com...
> >I have a *.bak file that is 1.7GB in size. When I do a restore the log file
> > is 3.0Gb and the data file is 16GB. Does anyone have any idea on what could
> > be causing this?
> > This is related to the other newsgroup post of "SQL 2005 db won't shrink".
> > Also, this db is a production Dynamics AX 4.0.
> > Any help/direction/info would be greatly appreciated.
>|||>A misunderstanding, so let me clarify...I need to remove all the "space
> available" from this db, no matter how it gets there.
Why, that comes with a huge performance penalty. But, assuming you have your reasons:
For the log to shrink, you need to work DBCC LOGINFO, BACKUP LOG and DBCC SHRINKFILE. See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp for details. Pay special attention to the DBCC
LOGINFO part.
For the data part, well, it should shrink using DBCC SHRINKFILE. IT can take a while to shrink 16
GB, but it should happen (no fancy options, though).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Patrick R." <PatrickR@.discussions.microsoft.com> wrote in message
news:69E7CBDE-6BA0-4234-9987-7D1AE5529275@.microsoft.com...
>A misunderstanding, so let me clarify...I need to remove all the "space
> available" from this db, no matter how it gets there. And I've tried all the
> shrinkdatabase, shrinkfiles, put in simple recover mode, backed up, restored,
> etc. And the space is still there. So what can remove the unwanted space?
> Thanks
> "Tibor Karaszi" wrote:
>> A database backup file only includes the data used in the database files and also the log records
>> produced while performing the backup. That backup was performed on a database which had free
>> space
>> in both database file and the log file. When you restore the backup, SQL Server has to create the
>> database with the same file size as all the database files had when the backup was performed. So,
>> what you see is perfectly normal.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Patrick R." <PatrickR@.discussions.microsoft.com> wrote in message
>> news:7429D298-69ED-444B-87E1-0659CF2F74B9@.microsoft.com...
>> >I have a *.bak file that is 1.7GB in size. When I do a restore the log file
>> > is 3.0Gb and the data file is 16GB. Does anyone have any idea on what could
>> > be causing this?
>> > This is related to the other newsgroup post of "SQL 2005 db won't shrink".
>> > Also, this db is a production Dynamics AX 4.0.
>> > Any help/direction/info would be greatly appreciated.
>>|||It is not the log file that has the issue, it's the db file. Also, I've
logged a case w/ MS and we've done all the things I've read and done earlier,
to no availl. I am currently uploading my bak to MS for their evaluation. I
will keep this thread updated on the results, and thank everyone for their
time and effort, thanks, Patrick
"Tibor Karaszi" wrote:
> >A misunderstanding, so let me clarify...I need to remove all the "space
> > available" from this db, no matter how it gets there.
> Why, that comes with a huge performance penalty. But, assuming you have your reasons:
> For the log to shrink, you need to work DBCC LOGINFO, BACKUP LOG and DBCC SHRINKFILE. See
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp for details. Pay special attention to the DBCC
> LOGINFO part.
> For the data part, well, it should shrink using DBCC SHRINKFILE. IT can take a while to shrink 16
> GB, but it should happen (no fancy options, though).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Patrick R." <PatrickR@.discussions.microsoft.com> wrote in message
> news:69E7CBDE-6BA0-4234-9987-7D1AE5529275@.microsoft.com...
> >A misunderstanding, so let me clarify...I need to remove all the "space
> > available" from this db, no matter how it gets there. And I've tried all the
> > shrinkdatabase, shrinkfiles, put in simple recover mode, backed up, restored,
> > etc. And the space is still there. So what can remove the unwanted space?
> > Thanks
> >
> > "Tibor Karaszi" wrote:
> >
> >> A database backup file only includes the data used in the database files and also the log records
> >> produced while performing the backup. That backup was performed on a database which had free
> >> space
> >> in both database file and the log file. When you restore the backup, SQL Server has to create the
> >> database with the same file size as all the database files had when the backup was performed. So,
> >> what you see is perfectly normal.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Patrick R." <PatrickR@.discussions.microsoft.com> wrote in message
> >> news:7429D298-69ED-444B-87E1-0659CF2F74B9@.microsoft.com...
> >> >I have a *.bak file that is 1.7GB in size. When I do a restore the log file
> >> > is 3.0Gb and the data file is 16GB. Does anyone have any idea on what could
> >> > be causing this?
> >> > This is related to the other newsgroup post of "SQL 2005 db won't shrink".
> >> > Also, this db is a production Dynamics AX 4.0.
> >> > Any help/direction/info would be greatly appreciated.
> >>
> >>
>

Thursday, March 22, 2012

DB updates when limited to bak/trn files only

Monday, March 19, 2012

DB restore error

DB Restore

DB Restore

DB Restore

Saturday, February 25, 2012

DB Maintenance Plan not working properly

DB Maintenance Plan not working properly

DB Maintenance Plan not working properly

Friday, February 17, 2012

DB is boated when restored

DB is boated when restored

DB is boated when restored

DBCC CHECKDB

Blog Archive

About Me