Basic Design Question Currency Dimension or New Measure

Hi All,
I am in the middle of a decision and I would like to ask experts for their
ideas.
I have Sales Reporting System on MSSQL 2000 + AS + RS
So far I have used only USD as reporting currency, now I am going to add
EURO and Local Currencies. My Current Model has "Sales", "Performance%" etc
as measures and Items,Countries etc as dimensions.
The question is how should I add new currencies to the system ?
As new Measures Sales_EURO?
or
As adding them to the fact table as rows keeping only one Sales measure and
adding a Currency dimension ?
I have around 2 million record in my fact table and data is accessed via
Reporting Services reports and via an application using OWC Pivot Table.
Thanks in advance?
erdalErdal,
Check out Adventure Works Enterprise Analysis services sample. I believe it
solves your problem via a currency dimension.
"erdal akbulut" <erdalim21@.yahoo.com> wrote in message
news:eJWeL32cGHA.4576@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I am in the middle of a decision and I would like to ask experts for their
> ideas.
> I have Sales Reporting System on MSSQL 2000 + AS + RS
> So far I have used only USD as reporting currency, now I am going to add
> EURO and Local Currencies. My Current Model has "Sales", "Performance%"
> etc
> as measures and Items,Countries etc as dimensions.
> The question is how should I add new currencies to the system ?
> As new Measures Sales_EURO?
> or
> As adding them to the fact table as rows keeping only one Sales measure
> and
> adding a Currency dimension ?
> I have around 2 million record in my fact table and data is accessed via
> Reporting Services reports and via an application using OWC Pivot Table.
> Thanks in advance?
> erdal
>
>
>|||Tim Thanks for you reply.
Unfortunately I dont have SSAS 2005 installed, think that sample comes with
SSAS 2005. Do you know is there any documentation for that project that I
can use without installing the application itself?
erdal
"Tim Dot NoSpam" <Tim.NoSpam@.hughes.net> wrote in message
news:eD1tkPdeGHA.380@.TK2MSFTNGP04.phx.gbl...
> Erdal,
> Check out Adventure Works Enterprise Analysis services sample. I believe
it
> solves your problem via a currency dimension.
> "erdal akbulut" <erdalim21@.yahoo.com> wrote in message
> news:eJWeL32cGHA.4576@.TK2MSFTNGP05.phx.gbl...
their[vbcol=seagreen]
>|||Not that I'm aware of. Sorry.
-Tim
"erdal akbulut" <erdalim21@.yahoo.com> wrote in message
news:uE7najleGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Tim Thanks for you reply.
> Unfortunately I dont have SSAS 2005 installed, think that sample comes
> with
> SSAS 2005. Do you know is there any documentation for that project that I
> can use without installing the application itself?
> erdal
> "Tim Dot NoSpam" <Tim.NoSpam@.hughes.net> wrote in message
> news:eD1tkPdeGHA.380@.TK2MSFTNGP04.phx.gbl...
> it
> their
>

Basic advice on good practise required

I have master tables that I will be updating from our ERP system. Some examples I have seen take an approach of dropping a table in SQL server then creating it again before importing; some, and probably my choice, append and update; I have not seen an example where records are all deleted then the data appended afterwards. Of the three approaches which is generally regarded as best practise / most efficient?

The create and re-load versus the delete and then append are really the same thing from a process perspective, but they will have different performance due to the overhead of data change in SQL Server. Delete is a logged operation, so incurs overhead by writing the transaction log each row that is deleted. Dropping the table is just much faster. Truncating the table would be similar to the drop. It is an non-logged, non-selective delete in effect.

As to which is faster of the two methods, re-load or partial load and update, it depends on lots of factors. The speed of extract will be constant since both methods require you to extract all data, so it will come down to the cost of inserting lots of data, lots of write activity versus the cost of comparison in trying to reduce that write to only the changes required. I cannot give answers as things like row overall row width, comparison columns and how much data needs to be moved around to do each method will impact this. Here is an article that looks at two methods of comparing tables.

Get all from Table A that isn't in Table B
(http://www.sqlis.com/311.aspx)

Another option is to just insert all data, and capture failures due to a primary key violation. Then for the failures check or updates. I did some tests of a similar approach for patching bad data, and it was generally quicker to let it fail the insert and fix afterwards that check and potentially fix all rows. This will depend on the ratio of good to bad, or in your case, new rows, old rows, old that need to be updated.

If the cost of updates is high, then you will want to check if an update is really required, or has nothing changed. The Checksum Transform can be useful for this, as comparing an integer is faster than moving and comparing 200 columns of data to detect and change. Again it depends on your data.

Checksum Transformation
(http://www.sqlis.com/21.aspx)

The thing you need to work out is where is the bottle neck in your process. It may be that the extract query or network transfer is the slowest part and it does not matter which method you use for downstream loading.

My opinion is always go for deltas if you can, but if not then delete (drop/truncate) and reload all. It can be harder with foreign keys but it is going to be faster I think.

sql

BadImageFormatException in tablet application

I'm currently developing a tablet application which uses SQL Mobile 2005.

I added the reference to the System.Data.SqlServerCe DLL and copied all the necessary DLLs of the SQL Mobile SDK (from the Program Files\Microsoft SQL Sevrver 2005 Mobile Edition\Device\Mobile\v3.0\tablet directory) to my output directory.

But when my code hits the first SQL Mobile related line:

SqlCeEngine dbEngine = new SqlCeEngine();

I get an BadImageFormatException. This happens both on the Tablet and my development machine. I already reinstalled the SQL Mobile 2005 Device SDK, both nothing changed after that.

Any ideas why this fails? Is it a bug in the System.Data.SqlServerCe DLL or the SDK?

I fixed this issue myself. Apparently I needed to copy the DLLs from Program Files\Microsoft Visual Studio 8\Common7\IDE, opposed to the directory mentioned in my first post.

Backward compatibility of Backups from 2005 to 2000

Hi folks
Just need a little advice. If we backup our new SQL 2005 database using
the SQL Server backup system might we have any issued restoring the
database to a 2000 server? We're not planning to have any complex DTS
or Replication jobs to deal with, so that shouldn't be an issue, but I
wasn't sure if there might be other issues to look out for.
much appreciated...
Laurence
Hi
You can not restore a SQL Server 2005 backup into SQL Server 2000. The DB
structures have changed slightly.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"LaurenceT" <laurencetruman@.gmail.com> wrote in message
news:1133782233.403165.130820@.g43g2000cwa.googlegr oups.com...
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
>
|||Sorry to be the bearer of bad news but you can't restore a SQL 2005 database
backup to SQL 2000. As you probably already know, you can restore from 2000
to 2005, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"LaurenceT" <laurencetruman@.gmail.com> wrote in message
news:1133782233.403165.130820@.g43g2000cwa.googlegr oups.com...
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
>
|||LaurenceT wrote:
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
You can't do that. What exactly do you want to achieve by backing up
and restoring between versions? Is this about availability or
deployment or something else? There ought to be some other solution.
David Portas
SQL Server MVP

Backward compatibility of Backups from 2005 to 2000

Hi folks
Just need a little advice. If we backup our new SQL 2005 database using
the SQL Server backup system might we have any issued restoring the
database to a 2000 server? We're not planning to have any complex DTS
or Replication jobs to deal with, so that shouldn't be an issue, but I
wasn't sure if there might be other issues to look out for.
much appreciated...
LaurenceHi
You can not restore a SQL Server 2005 backup into SQL Server 2000. The DB
structures have changed slightly.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"LaurenceT" <laurencetruman@.gmail.com> wrote in message
news:1133782233.403165.130820@.g43g2000cwa.googlegroups.com...
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
>|||Sorry to be the bearer of bad news but you can't restore a SQL 2005 database
backup to SQL 2000. As you probably already know, you can restore from 2000
to 2005, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"LaurenceT" <laurencetruman@.gmail.com> wrote in message
news:1133782233.403165.130820@.g43g2000cwa.googlegroups.com...
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
>|||LaurenceT wrote:
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
You can't do that. What exactly do you want to achieve by backing up
and restoring between versions? Is this about availability or
deployment or something else? There ought to be some other solution.
David Portas
SQL Server MVP
--

Backward compatibility of Backups from 2005 to 2000

Hi folks
Just need a little advice. If we backup our new SQL 2005 database using
the SQL Server backup system might we have any issued restoring the
database to a 2000 server? We're not planning to have any complex DTS
or Replication jobs to deal with, so that shouldn't be an issue, but I
wasn't sure if there might be other issues to look out for.
much appreciated...
LaurenceHi
You can not restore a SQL Server 2005 backup into SQL Server 2000. The DB
structures have changed slightly.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"LaurenceT" <laurencetruman@.gmail.com> wrote in message
news:1133782233.403165.130820@.g43g2000cwa.googlegroups.com...
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
>|||Sorry to be the bearer of bad news but you can't restore a SQL 2005 database
backup to SQL 2000. As you probably already know, you can restore from 2000
to 2005, though.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"LaurenceT" <laurencetruman@.gmail.com> wrote in message
news:1133782233.403165.130820@.g43g2000cwa.googlegroups.com...
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
>|||LaurenceT wrote:
> Hi folks
> Just need a little advice. If we backup our new SQL 2005 database using
> the SQL Server backup system might we have any issued restoring the
> database to a 2000 server? We're not planning to have any complex DTS
> or Replication jobs to deal with, so that shouldn't be an issue, but I
> wasn't sure if there might be other issues to look out for.
> much appreciated...
> Laurence
You can't do that. What exactly do you want to achieve by backing up
and restoring between versions? Is this about availability or
deployment or something else? There ought to be some other solution.
--
David Portas
SQL Server MVP
--

Backups: Syncing File and Database backups

Hi,
What's the best practice when trying to synchronize the backup of a set of
databases and a set of files on the file system?
I currently create backup schedules using the Maintenance Plan wizard. Is
there a way to trigger an event when the scheduled backup completes? Or is
there a better way to do this?
Thanks!
RossHi
You can add additional steps to the maintainance plan's backup job, or
alternatively you may want to start the job from batch script run by the
windows scheduler and run xp_sqlmaint (the command executed by the job step
of the maintainance job) though isql.
John
"Ross" wrote:
> Hi,
> What's the best practice when trying to synchronize the backup of a set of
> databases and a set of files on the file system?
> I currently create backup schedules using the Maintenance Plan wizard. Is
> there a way to trigger an event when the scheduled backup completes? Or is
> there a better way to do this?
> Thanks!
> Ross

Backups using SQL Server

Is it possible to backup a remote SQL Server database to my local system? It is not working for me as I'm getting error as given below

Cannot open backup device 'path'. Device error or device offline

Any idea??

No. MSSQL only backs up locally. You could have it save to an ftp directory and download it that way.

Have a good day.|||

I tried but it didn't worked. Can you post the steps here?

|||I you are using the SQL Manager use should be able to right click on the database that you wish to back up and hover over all tasks. Then select backup, This will bring up the screen for backups. On that screen click the select device button and set the device to the directory that you want to backup to. Then name the file whatever.bak. Start the backup and you are done. Open up the folder and the file will be where you selected it to save to. If that folder is available via FTP then you can download it.

I hope that this helps.

Backups Failing on Production Servers

Hello,
Could you tell me please if there is something a DBA is supposed to do to
override an operating system error that states the device is offline and the
backups fail? The error is as follows:
BackupDiskFile::CreateMedia: Backup device
'F:\mssql\backup\PES_PortfolioEdge backup.dmp' failed to create. Operating
system error = 32(The process cannot access the file because it is being used
by another process.).
Note*There was a job running that was doing some db activity, but this
should not prevent a backup from occurring. The job was running from 9:30 PM
to 11:30 PM and the dump starts at 11 PM.
BACKUP failed to complete the command BACKUP DATABASE [PES_PortfolioEdge] TO
DISK = N'F:\mssql\backup\PES_PortfolioEdge backup.dmp' WITH INIT ,
NOUNLOAD , NAME = N'PES_PortfolioEdge backup', NOSKIP , STATS = 10,
NOFORMAT
How do you override an operating system error if it is possible to be sure
that production dumps are successful? Is there something the DBA can do?
Thanks
Your message is telling you that another process still has a hold of your
file.
You would need to figure out what process has a hold of it. I would go ask
whoever is in charge of putting your backups onto tape if his job is still
running. I used to always have these when my guy was trying to read from my
backup device to put my backup on tape, but I was trying to write to the file
to do a backup.
ChrisR
"Cynthia" wrote:

> Hello,
> Could you tell me please if there is something a DBA is supposed to do to
> override an operating system error that states the device is offline and the
> backups fail? The error is as follows:
> BackupDiskFile::CreateMedia: Backup device
> 'F:\mssql\backup\PES_PortfolioEdge backup.dmp' failed to create. Operating
> system error = 32(The process cannot access the file because it is being used
> by another process.).
> Note*There was a job running that was doing some db activity, but this
> should not prevent a backup from occurring. The job was running from 9:30 PM
> to 11:30 PM and the dump starts at 11 PM.
> BACKUP failed to complete the command BACKUP DATABASE [PES_PortfolioEdge] TO
> DISK = N'F:\mssql\backup\PES_PortfolioEdge backup.dmp' WITH INIT ,
> NOUNLOAD , NAME = N'PES_PortfolioEdge backup', NOSKIP , STATS = 10,
> NOFORMAT
> How do you override an operating system error if it is possible to be sure
> that production dumps are successful? Is there something the DBA can do?
> Thanks
>

Backups Failing on Production Servers

Hello,
Could you tell me please if there is something a DBA is supposed to do to
override an operating system error that states the device is offline and the
backups fail? The error is as follows:
BackupDiskFile::CreateMedia: Backup device
'F:\mssql\backup\PES_PortfolioEdge backup.dmp' failed to create. Operating
system error = 32(The process cannot access the file because it is being used
by another process.).
Note*There was a job running that was doing some db activity, but this
should not prevent a backup from occurring. The job was running from 9:30 PM
to 11:30 PM and the dump starts at 11 PM.
BACKUP failed to complete the command BACKUP DATABASE [PES_PortfolioEdge] TO
DISK = N'F:\mssql\backup\PES_PortfolioEdge backup.dmp' WITH INIT ,
NOUNLOAD , NAME = N'PES_PortfolioEdge backup', NOSKIP , STATS = 10,
NOFORMAT
How do you override an operating system error if it is possible to be sure
that production dumps are successful? Is there something the DBA can do?
ThanksYour message is telling you that another process still has a hold of your
file.
You would need to figure out what process has a hold of it. I would go ask
whoever is in charge of putting your backups onto tape if his job is still
running. I used to always have these when my guy was trying to read from my
backup device to put my backup on tape, but I was trying to write to the file
to do a backup.
--
ChrisR
"Cynthia" wrote:
> Hello,
> Could you tell me please if there is something a DBA is supposed to do to
> override an operating system error that states the device is offline and the
> backups fail? The error is as follows:
> BackupDiskFile::CreateMedia: Backup device
> 'F:\mssql\backup\PES_PortfolioEdge backup.dmp' failed to create. Operating
> system error = 32(The process cannot access the file because it is being used
> by another process.).
> Note*There was a job running that was doing some db activity, but this
> should not prevent a backup from occurring. The job was running from 9:30 PM
> to 11:30 PM and the dump starts at 11 PM.
> BACKUP failed to complete the command BACKUP DATABASE [PES_PortfolioEdge] TO
> DISK = N'F:\mssql\backup\PES_PortfolioEdge backup.dmp' WITH INIT ,
> NOUNLOAD , NAME = N'PES_PortfolioEdge backup', NOSKIP , STATS = 10,
> NOFORMAT
> How do you override an operating system error if it is possible to be sure
> that production dumps are successful? Is there something the DBA can do?
> Thanks
>

Backups Failing on Production Servers

Hello,
Could you tell me please if there is something a DBA is supposed to do to
override an operating system error that states the device is offline and the
backups fail? The error is as follows:
BackupDiskFile::CreateMedia: Backup device
'F:\mssql\backup\PES_PortfolioEdge backup.dmp' failed to create. Operating
system error = 32(The process cannot access the file because it is being use
d
by another process.).
Note*There was a job running that was doing some db activity, but this
should not prevent a backup from occurring. The job was running from 9:30 PM
to 11:30 PM and the dump starts at 11 PM.
BACKUP failed to complete the command BACKUP DATABASE [PES_PortfolioEdge
] TO
DISK = N'F:\mssql\backup\PES_PortfolioEdge backup.dmp' WITH INIT ,
NOUNLOAD , NAME = N'PES_PortfolioEdge backup', NOSKIP , STATS = 10,
NOFORMAT
How do you override an operating system error if it is possible to be sure
that production dumps are successful? Is there something the DBA can do?
ThanksYour message is telling you that another process still has a hold of your
file.
You would need to figure out what process has a hold of it. I would go ask
whoever is in charge of putting your backups onto tape if his job is still
running. I used to always have these when my guy was trying to read from my
backup device to put my backup on tape, but I was trying to write to the fil
e
to do a backup.
ChrisR
"Cynthia" wrote:

> Hello,
> Could you tell me please if there is something a DBA is supposed to do to
> override an operating system error that states the device is offline and t
he
> backups fail? The error is as follows:
> BackupDiskFile::CreateMedia: Backup device
> 'F:\mssql\backup\PES_PortfolioEdge backup.dmp' failed to create. Operating
> system error = 32(The process cannot access the file because it is being u
sed
> by another process.).
> Note*There was a job running that was doing some db activity, but this
> should not prevent a backup from occurring. The job was running from 9:30
PM
> to 11:30 PM and the dump starts at 11 PM.
> BACKUP failed to complete the command BACKUP DATABASE [PES_PortfolioEd
ge] TO
> DISK = N'F:\mssql\backup\PES_PortfolioEdge backup.dmp' WITH INIT ,
> NOUNLOAD , NAME = N'PES_PortfolioEdge backup', NOSKIP , STATS = 10,
> NOFORMAT
> How do you override an operating system error if it is possible to be sure
> that production dumps are successful? Is there something the DBA can do?
> Thanks
>

BackupDiskFile::CreateMedia: Backup device failed to create.

Hi there,
I am getting this error message:
BackupDiskFile::CreateMedia: Backup device failed to create. Operating
system error = 1130(Not enough server storage is available to process this
command.).
Someone has already posted this question below.
http://groups.google.com/group/microsoft.public.sqlserver.dts/browse_thread/thread/edcd04268b1d040e/d438e8317739acd8?lnk=st&q=BackupDiskFile%3A%3ACreateMedia%3A+Backup+device+failed+to+create.+Operating+system+error+%3D+1130(Not+enough+server+storage+is+available+to+process+this+command.).&rnum=1&hl=en#d438e8317739acd8
I checked our security and it is alright. Also, I won't get this error if I
run manually and I am little bit confused.
Could you help?
Thanks.
Abel ChanHello Abel,
First, please make sure you have the latest OS/SQL server service pack.
Also, I found similar issue is resolved by incresae size of IRPStacksize.
Please refer to the following article to test
285089 IRPStackSize Parameter in Windows 2000
http://support.microsoft.com/?id=285089
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>Thread-Topic: BackupDiskFile::CreateMedia: Backup device failed to create.
>thread-index: AcYzStfdEVNmKflURjaV99MKSuljqQ==>X-WBNR-Posting-Host: 206.253.218.194
>From: =?Utf-8?B?QWJlbCBDaGFu?= <awong@.newsgroup.nospam>
>Subject: BackupDiskFile::CreateMedia: Backup device failed to create.
>Date: Thu, 16 Feb 2006 14:46:36 -0800
>Lines: 19
>Message-ID: <6155BC54-E793-4FA0-9FF0-B8C03E93F9FF@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:421397
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Hi there,
>I am getting this error message:
>BackupDiskFile::CreateMedia: Backup device failed to create. Operating
>system error = 1130(Not enough server storage is available to process this
>command.).
>Someone has already posted this question below.
http://groups.google.com/group/microsoft.public.sqlserver.dts/browse_thread/
thread/edcd04268b1d040e/d438e8317739acd8?lnk=st&q=BackupDiskFile%3A%3ACreate
Media%3A+Backup+device+failed+to+create.+Operating+system+error+%3D+1130(Not
+enough+server+storage+is+available+to+process+this+command.).&rnum=1&hl=en#
d438e8317739acd8
>I checked our security and it is alright. Also, I won't get this error if
I
>run manually and I am little bit confused.
>Could you help?
>Thanks.
>Abel Chan
>

Backup yielding Operatin System Error 33?

A little help please?
I have a 14.2 GB database that has stopped backing up and yields the
following errors in the Event log and SQL Server Log respectively:
Application Event Log
18210 :
BackupMedium::ReportIoError: write failure on backup device 'C:\Program
Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Manual.bak'.
Operating system error 33(The process cannot access the file because another
process has locked a portion of the file.).
SQL Server Error Log:
Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\enDataWH_db_200601042204.BAK
Is there any reasonable way to fix this?
Thanks in advance for your help.I've seen that message when trying to backup to a compressed volume.
If that's the case, this may help :-
http://support.microsoft.com/?id=231347
--
HTH. Ryan
"Dave Smith" <DaveSmith@.discussions.microsoft.com> wrote in message
news:255EB00C-6217-4471-BC1B-ECAC8E935F03@.microsoft.com...
>A little help please?
> I have a 14.2 GB database that has stopped backing up and yields the
> following errors in the Event log and SQL Server Log respectively:
> Application Event Log
> 18210 :
> BackupMedium::ReportIoError: write failure on backup device 'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Manual.bak'.
> Operating system error 33(The process cannot access the file because
> another
> process has locked a portion of the file.).
> SQL Server Error Log:
> Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
> 983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
> Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft
> SQL
> Server\MSSQL\BACKUP\enDataWH_db_200601042204.BAK
> Is there any reasonable way to fix this?
> Thanks in advance for your help.
>|||Dave,
You can try to identify the process that has locked the file. It is
possible that a third party software like tape backup has acquired a read
lock on the file.
1) use "net file " command to see open files
"net file id /close " to close a open file
2) Use third party tools like filemon from sysinternals to do the same
You can also see if there are no active sql server processes or agent jobs
that may be accessing the file.
Another workaround is to rename the existing file and try the backup again
assuming that you are able to rename and there is space for additional backup.
"Dave Smith" wrote:
> A little help please?
> I have a 14.2 GB database that has stopped backing up and yields the
> following errors in the Event log and SQL Server Log respectively:
> Application Event Log
> 18210 :
> BackupMedium::ReportIoError: write failure on backup device 'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Manual.bak'.
> Operating system error 33(The process cannot access the file because another
> process has locked a portion of the file.).
> SQL Server Error Log:
> Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
> 983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
> Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\enDataWH_db_200601042204.BAK
> Is there any reasonable way to fix this?
> Thanks in advance for your help.
>|||Way to go Ryan!!
That was exactly the problem. Out MSSQL\BACKUP folder was set to be
compressed.
FYI, the job does a DBCC CHECKDB, Full Backup, and Verify. It used to fail
after 38 to 44 minutes. it just succeeded in 18:03!!
YOU are the man!
Thanks a bunch,
Dave Smith
"Ryan" wrote:
> I've seen that message when trying to backup to a compressed volume.
> If that's the case, this may help :-
> http://support.microsoft.com/?id=231347
> --
> HTH. Ryan
>
> "Dave Smith" <DaveSmith@.discussions.microsoft.com> wrote in message
> news:255EB00C-6217-4471-BC1B-ECAC8E935F03@.microsoft.com...
> >A little help please?
> >
> > I have a 14.2 GB database that has stopped backing up and yields the
> > following errors in the Event log and SQL Server Log respectively:
> >
> > Application Event Log
> > 18210 :
> > BackupMedium::ReportIoError: write failure on backup device 'C:\Program
> > Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Manual.bak'.
> > Operating system error 33(The process cannot access the file because
> > another
> > process has locked a portion of the file.).
> >
> > SQL Server Error Log:
> > Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
> > 983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
> > Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
> > m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft
> > SQL
> > Server\MSSQL\BACKUP\enDataWH_db_200601042204.BAK
> >
> > Is there any reasonable way to fix this?
> >
> > Thanks in advance for your help.
> >
> >
>
>

Backup yielding Operatin System Error 33?

A little help please?
I have a 14.2 GB database that has stopped backing up and yields the
following errors in the Event log and SQL Server Log respectively:
Application Event Log
18210 :
BackupMedium::ReportIoError: write failure on backup device 'C:\Program
Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Ma
nual.bak'.
Operating system error 33(The process cannot access the file because another
process has locked a portion of the file.).
SQL Server Error Log:
Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\enDataWH_db_20060104
2204.BAK
Is there any reasonable way to fix this?
Thanks in advance for your help.I've seen that message when trying to backup to a compressed volume.
If that's the case, this may help :-
http://support.microsoft.com/?id=231347
HTH. Ryan
"Dave Smith" <DaveSmith@.discussions.microsoft.com> wrote in message
news:255EB00C-6217-4471-BC1B-ECAC8E935F03@.microsoft.com...
>A little help please?
> I have a 14.2 GB database that has stopped backing up and yields the
> following errors in the Event log and SQL Server Log respectively:
> Application Event Log
> 18210 :
> BackupMedium::ReportIoError: write failure on backup device 'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Ma
nual.bak'.
> Operating system error 33(The process cannot access the file because
> another
> process has locked a portion of the file.).
> SQL Server Error Log:
> Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
> 983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
> Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft
> SQL
> Server\MSSQL\BACKUP\enDataWH_db_20060104
2204.BAK
> Is there any reasonable way to fix this?
> Thanks in advance for your help.
>|||Dave,
You can try to identify the process that has locked the file. It is
possible that a third party software like tape backup has acquired a read
lock on the file.
1) use "net file " command to see open files
"net file id /close " to close a open file
2) Use third party tools like filemon from sysinternals to do the same
You can also see if there are no active sql server processes or agent jobs
that may be accessing the file.
Another workaround is to rename the existing file and try the backup again
assuming that you are able to rename and there is space for additional backu
p.
"Dave Smith" wrote:

> A little help please?
> I have a 14.2 GB database that has stopped backing up and yields the
> following errors in the Event log and SQL Server Log respectively:
> Application Event Log
> 18210 :
> BackupMedium::ReportIoError: write failure on backup device 'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Ma
nual.bak'.
> Operating system error 33(The process cannot access the file because anoth
er
> process has locked a portion of the file.).
> SQL Server Error Log:
> Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
> 983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
> Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft SQ
L
> Server\MSSQL\BACKUP\enDataWH_db_20060104
2204.BAK
> Is there any reasonable way to fix this?
> Thanks in advance for your help.
>|||Way to go Ryan!!
That was exactly the problem. Out MSSQL\BACKUP folder was set to be
compressed.
FYI, the job does a DBCC CHECKDB, Full Backup, and Verify. It used to fail
after 38 to 44 minutes. it just succeeded in 18:03!!
YOU are the man!
Thanks a bunch,
Dave Smith
"Ryan" wrote:

> I've seen that message when trying to backup to a compressed volume.
> If that's the case, this may help :-
> http://support.microsoft.com/?id=231347
> --
> HTH. Ryan
>
> "Dave Smith" <DaveSmith@.discussions.microsoft.com> wrote in message
> news:255EB00C-6217-4471-BC1B-ECAC8E935F03@.microsoft.com...
>
>

Backup yielding Operatin System Error 33?

A little help please?
I have a 14.2 GB database that has stopped backing up and yields the
following errors in the Event log and SQL Server Log respectively:
Application Event Log
18210 :
BackupMedium::ReportIoError: write failure on backup device 'C:\Program
Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Manual.bak'.
Operating system error 33(The process cannot access the file because another
process has locked a portion of the file.).
SQL Server Error Log:
Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\enDataWH_db_200601042204.BAK
Is there any reasonable way to fix this?
Thanks in advance for your help.
I've seen that message when trying to backup to a compressed volume.
If that's the case, this may help :-
http://support.microsoft.com/?id=231347
HTH. Ryan
"Dave Smith" <DaveSmith@.discussions.microsoft.com> wrote in message
news:255EB00C-6217-4471-BC1B-ECAC8E935F03@.microsoft.com...
>A little help please?
> I have a 14.2 GB database that has stopped backing up and yields the
> following errors in the Event log and SQL Server Log respectively:
> Application Event Log
> 18210 :
> BackupMedium::ReportIoError: write failure on backup device 'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Manual.bak'.
> Operating system error 33(The process cannot access the file because
> another
> process has locked a portion of the file.).
> SQL Server Error Log:
> Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
> 983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
> Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft
> SQL
> Server\MSSQL\BACKUP\enDataWH_db_200601042204.BAK
> Is there any reasonable way to fix this?
> Thanks in advance for your help.
>
|||Dave,
You can try to identify the process that has locked the file. It is
possible that a third party software like tape backup has acquired a read
lock on the file.
1) use "net file " command to see open files
"net file id /close " to close a open file
2) Use third party tools like filemon from sysinternals to do the same
You can also see if there are no active sql server processes or agent jobs
that may be accessing the file.
Another workaround is to rename the existing file and try the backup again
assuming that you are able to rename and there is space for additional backup.
"Dave Smith" wrote:

> A little help please?
> I have a 14.2 GB database that has stopped backing up and yields the
> following errors in the Event log and SQL Server Log respectively:
> Application Event Log
> 18210 :
> BackupMedium::ReportIoError: write failure on backup device 'C:\Program
> Files\Microsoft SQL Server\MSSQL\BACKUP\enDataWH_20060401_Manual.bak'.
> Operating system error 33(The process cannot access the file because another
> process has locked a portion of the file.).
> SQL Server Error Log:
> Internal I/O request 0x2775DCF8: Op: Write, pBuffer: 0x062E0000, Size:
> 983040, Position: 10592131584, UMS: Internal: 0x103, InternalHigh: 0x0,
> Offset: 0x77571A00, OffsetHigh: 0x2, m_buf: 0x062E0000, m_len: 983040,
> m_actualBytes: 0, m_errcode: 33, BackupFile: C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\enDataWH_db_200601042204.BAK
> Is there any reasonable way to fix this?
> Thanks in advance for your help.
>
|||Way to go Ryan!!
That was exactly the problem. Out MSSQL\BACKUP folder was set to be
compressed.
FYI, the job does a DBCC CHECKDB, Full Backup, and Verify. It used to fail
after 38 to 44 minutes. it just succeeded in 18:03!!
YOU are the man!
Thanks a bunch,
Dave Smith
"Ryan" wrote:

> I've seen that message when trying to backup to a compressed volume.
> If that's the case, this may help :-
> http://support.microsoft.com/?id=231347
> --
> HTH. Ryan
>
> "Dave Smith" <DaveSmith@.discussions.microsoft.com> wrote in message
> news:255EB00C-6217-4471-BC1B-ECAC8E935F03@.microsoft.com...
>
>

Backup will not Restore to different Instance

I dumped a database on the Prod system and tried to restore it to the Dev
system. I have done this before, but this time the Restore sits on the
'Restore Progress' window forever, with sqlserver using 1% cpu. EM shows the
'Restore Database' sessions with an unchanging 422 I/O. The Sql Server Log's
last entry is "Starting up database 'dbname'. There is no indication that
the database is actually restoring.
What the heck is happening?Have you tried to kill the spid and restart the restore?
Andrew J. Kelly SQL MVP
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:747DA3AB-46B4-476C-A54B-B07C5C33D9C0@.microsoft.com...
>I dumped a database on the Prod system and tried to restore it to the Dev
> system. I have done this before, but this time the Restore sits on the
> 'Restore Progress' window forever, with sqlserver using 1% cpu. EM shows
> the
> 'Restore Database' sessions with an unchanging 422 I/O. The Sql Server
> Log's
> last entry is "Starting up database 'dbname'. There is no indication
> that
> the database is actually restoring.
> What the heck is happening?|||Andrew,
Yes, I have. I have also cycled the Instance. When I begin the restore, the
database files are allocated but the progress window remains unchanged for a
long period of time. Alsom the instance does not appear to be using CPU or
disk. The backup was taken under 8.00.534 and the restore is under 8.00.818.
Perhaps that is the problem?
Michael
"Andrew J. Kelly" wrote:
> Have you tried to kill the spid and restart the restore?
>
> --
> Andrew J. Kelly SQL MVP
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:747DA3AB-46B4-476C-A54B-B07C5C33D9C0@.microsoft.com...
> >I dumped a database on the Prod system and tried to restore it to the Dev
> > system. I have done this before, but this time the Restore sits on the
> > 'Restore Progress' window forever, with sqlserver using 1% cpu. EM shows
> > the
> > 'Restore Database' sessions with an unchanging 422 I/O. The Sql Server
> > Log's
> > last entry is "Starting up database 'dbname'. There is no indication
> > that
> > the database is actually restoring.
> >
> > What the heck is happening?
>
>|||As a side-note, the database I am trying to restore into is created prior to
beginning the install. That database shows under EM like one would expect
until the Restore begins. Then it seems to disappear from EM (running on
another machine) and does not appear in sysdatabases.
"Snake" wrote:
> I dumped a database on the Prod system and tried to restore it to the Dev
> system. I have done this before, but this time the Restore sits on the
> 'Restore Progress' window forever, with sqlserver using 1% cpu. EM shows the
> 'Restore Database' sessions with an unchanging 422 I/O. The Sql Server Log's
> last entry is "Starting up database 'dbname'. There is no indication that
> the database is actually restoring.
> What the heck is happening?|||How large are the files? It can take a while to zero out large data files.
--
Andrew J. Kelly SQL MVP
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:1FC7C123-BDF5-432A-996F-79FABFBC8BE5@.microsoft.com...
> Andrew,
> Yes, I have. I have also cycled the Instance. When I begin the restore,
> the
> database files are allocated but the progress window remains unchanged for
> a
> long period of time. Alsom the instance does not appear to be using CPU or
> disk. The backup was taken under 8.00.534 and the restore is under
> 8.00.818.
> Perhaps that is the problem?
> Michael
> "Andrew J. Kelly" wrote:
>> Have you tried to kill the spid and restart the restore?
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Snake" <Snake@.discussions.microsoft.com> wrote in message
>> news:747DA3AB-46B4-476C-A54B-B07C5C33D9C0@.microsoft.com...
>> >I dumped a database on the Prod system and tried to restore it to the
>> >Dev
>> > system. I have done this before, but this time the Restore sits on the
>> > 'Restore Progress' window forever, with sqlserver using 1% cpu. EM
>> > shows
>> > the
>> > 'Restore Database' sessions with an unchanging 422 I/O. The Sql Server
>> > Log's
>> > last entry is "Starting up database 'dbname'. There is no indication
>> > that
>> > the database is actually restoring.
>> >
>> > What the heck is happening?
>>

Backup will not Restore to different Instance

I dumped a database on the Prod system and tried to restore it to the Dev
system. I have done this before, but this time the Restore sits on the
'Restore Progress' window forever, with sqlserver using 1% cpu. EM shows th
e
'Restore Database' sessions with an unchanging 422 I/O. The Sql Server Log'
s
last entry is "Starting up database 'dbname'. There is no indication that
the database is actually restoring.
What the heck is happening?Have you tried to kill the spid and restart the restore?
Andrew J. Kelly SQL MVP
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:747DA3AB-46B4-476C-A54B-B07C5C33D9C0@.microsoft.com...
>I dumped a database on the Prod system and tried to restore it to the Dev
> system. I have done this before, but this time the Restore sits on the
> 'Restore Progress' window forever, with sqlserver using 1% cpu. EM shows
> the
> 'Restore Database' sessions with an unchanging 422 I/O. The Sql Server
> Log's
> last entry is "Starting up database 'dbname'. There is no indication
> that
> the database is actually restoring.
> What the heck is happening?|||Andrew,
Yes, I have. I have also cycled the Instance. When I begin the restore, the
database files are allocated but the progress window remains unchanged for a
long period of time. Alsom the instance does not appear to be using CPU or
disk. The backup was taken under 8.00.534 and the restore is under 8.00.818
.
Perhaps that is the problem?
Michael
"Andrew J. Kelly" wrote:

> Have you tried to kill the spid and restart the restore?
>
> --
> Andrew J. Kelly SQL MVP
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:747DA3AB-46B4-476C-A54B-B07C5C33D9C0@.microsoft.com...
>
>|||As a side-note, the database I am trying to restore into is created prior to
beginning the install. That database shows under EM like one would expect
until the Restore begins. Then it seems to disappear from EM (running on
another machine) and does not appear in sysdatabases.
"Snake" wrote:

> I dumped a database on the Prod system and tried to restore it to the Dev
> system. I have done this before, but this time the Restore sits on the
> 'Restore Progress' window forever, with sqlserver using 1% cpu. EM shows
the
> 'Restore Database' sessions with an unchanging 422 I/O. The Sql Server Lo
g's
> last entry is "Starting up database 'dbname'. There is no indication th
at
> the database is actually restoring.
> What the heck is happening?|||How large are the files? It can take a while to zero out large data files.
Andrew J. Kelly SQL MVP
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:1FC7C123-BDF5-432A-996F-79FABFBC8BE5@.microsoft.com...[vbcol=seagreen]
> Andrew,
> Yes, I have. I have also cycled the Instance. When I begin the restore,
> the
> database files are allocated but the progress window remains unchanged for
> a
> long period of time. Alsom the instance does not appear to be using CPU or
> disk. The backup was taken under 8.00.534 and the restore is under
> 8.00.818.
> Perhaps that is the problem?
> Michael
> "Andrew J. Kelly" wrote:
>

Backup will not Restore to different Instance

I dumped a database on the Prod system and tried to restore it to the Dev
system. I have done this before, but this time the Restore sits on the
'Restore Progress' window forever, with sqlserver using 1% cpu. EM shows the
'Restore Database' sessions with an unchanging 422 I/O. The Sql Server Log's
last entry is "Starting up database 'dbname'. There is no indication that
the database is actually restoring.
What the heck is happening?
Have you tried to kill the spid and restart the restore?
Andrew J. Kelly SQL MVP
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:747DA3AB-46B4-476C-A54B-B07C5C33D9C0@.microsoft.com...
>I dumped a database on the Prod system and tried to restore it to the Dev
> system. I have done this before, but this time the Restore sits on the
> 'Restore Progress' window forever, with sqlserver using 1% cpu. EM shows
> the
> 'Restore Database' sessions with an unchanging 422 I/O. The Sql Server
> Log's
> last entry is "Starting up database 'dbname'. There is no indication
> that
> the database is actually restoring.
> What the heck is happening?
|||Andrew,
Yes, I have. I have also cycled the Instance. When I begin the restore, the
database files are allocated but the progress window remains unchanged for a
long period of time. Alsom the instance does not appear to be using CPU or
disk. The backup was taken under 8.00.534 and the restore is under 8.00.818.
Perhaps that is the problem?
Michael
"Andrew J. Kelly" wrote:

> Have you tried to kill the spid and restart the restore?
>
> --
> Andrew J. Kelly SQL MVP
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:747DA3AB-46B4-476C-A54B-B07C5C33D9C0@.microsoft.com...
>
>
|||As a side-note, the database I am trying to restore into is created prior to
beginning the install. That database shows under EM like one would expect
until the Restore begins. Then it seems to disappear from EM (running on
another machine) and does not appear in sysdatabases.
"Snake" wrote:

> I dumped a database on the Prod system and tried to restore it to the Dev
> system. I have done this before, but this time the Restore sits on the
> 'Restore Progress' window forever, with sqlserver using 1% cpu. EM shows the
> 'Restore Database' sessions with an unchanging 422 I/O. The Sql Server Log's
> last entry is "Starting up database 'dbname'. There is no indication that
> the database is actually restoring.
> What the heck is happening?
|||How large are the files? It can take a while to zero out large data files.
Andrew J. Kelly SQL MVP
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:1FC7C123-BDF5-432A-996F-79FABFBC8BE5@.microsoft.com...[vbcol=seagreen]
> Andrew,
> Yes, I have. I have also cycled the Instance. When I begin the restore,
> the
> database files are allocated but the progress window remains unchanged for
> a
> long period of time. Alsom the instance does not appear to be using CPU or
> disk. The backup was taken under 8.00.534 and the restore is under
> 8.00.818.
> Perhaps that is the problem?
> Michael
> "Andrew J. Kelly" wrote:

Backup trouble with system database model

Hello all,
Al of a sudden I cannot backup my model database.
When I try to backup the database I get the following message:
Server: Msg 9002, Level 17, State 6, Line 1
The log file for database 'model' is full. Back up the transaction log for t
he database to free up some log space.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Could not write a CHECKPOINT record in database ID 3 because the log is out
of space.
I have 2 questions:
(1) To my knowledge the model database has not been changed since the SQL se
rver installation (+/- 1 year ago), accept a weekly "Optimization". The mode
l database has recovery model "Simple". How did the log file get filled?
(2) How do I empty the log file? I have tried to empty the log file via:
--> DUMP TRANSACTION model WITH NO_LOG
--> BACKUP LOG model WITH NO_LOG
However with no success.
Any suggestion is welcome, thanks,
Annick van den BroekHi,
Have a look into this article;
http://support.microsoft.com/defaul...kb;en-us;317375
Thanks
Hari
MCDBA
"Annick van den Broek" <anonymous@.discussions.microsoft.com> wrote in
message news:5F6BC6C8-BFCD-4A7C-96A4-753A3F27AF12@.microsoft.com...
> Hello all,
> Al of a sudden I cannot backup my model database.
> When I try to backup the database I get the following message:
> Server: Msg 9002, Level 17, State 6, Line 1
> The log file for database 'model' is full. Back up the transaction log for
the database to free up some log space.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP DATABASE is terminating abnormally.
> Could not write a CHECKPOINT record in database ID 3 because the log is
out of space.
> I have 2 questions:
> (1) To my knowledge the model database has not been changed since the SQL
server installation (+/- 1 year ago), accept a weekly "Optimization". The
model database has recovery model "Simple". How did the log file get filled?
> (2) How do I empty the log file? I have tried to empty the log file via:
> --> DUMP TRANSACTION model WITH NO_LOG
> --> BACKUP LOG model WITH NO_LOG
> However with no success.
> Any suggestion is welcome, thanks,
> Annick van den Broek