Cannot restore database: files

Showing posts with label files. Show all posts

Sunday, March 25, 2012

can't attach 2 sample files can attach other files

I got SQL Server 2007 Express configured and I ran a few queries. It
appears to be configured correctly. I am trying to attach the
Northwind and Pubs example files from SQL Server 2000. I have tried
more than several times to attach them. I have them in the data file
of SQL Server 2007 express. The files are not read only. I looked
in the permissions and they were but I changed it to writeable and
checked the other files the log file and such.
When I try to attach the files I get the error message "Database
Northwind cannot be upgraded because it is read only or has read only
files. Make the files writeable and re-run recovery".
What is recovery? Do the files have to be upgraded from SQL SErver
2000?
thanks,Hi Janis,
Is that SQL Server 2005? (There is no SQL Server 2007). Have you changed the
permissions of both data and log files?
You could also try this
Northwind and pubs Sample Databases for SQL Server 2000
http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en
Hope this helps,
Ben Nevarez
"Janis Rough" wrote:
> I got SQL Server 2007 Express configured and I ran a few queries. It
> appears to be configured correctly. I am trying to attach the
> Northwind and Pubs example files from SQL Server 2000. I have tried
> more than several times to attach them. I have them in the data file
> of SQL Server 2007 express. The files are not read only. I looked
> in the permissions and they were but I changed it to writeable and
> checked the other files the log file and such.
> When I try to attach the files I get the error message "Database
> Northwind cannot be upgraded because it is read only or has read only
> files. Make the files writeable and re-run recovery".
> What is recovery? Do the files have to be upgraded from SQL SErver
> 2000?
> thanks,
>|||On Jan 30, 7:10 pm, Ben Nevarez
<bneva...@.no.spam.please.sunamerica.com> wrote:
> Hi Janis,
> Is that SQL Server 2005? (There is no SQL Server 2007). Have you changed the
> permissions of both data and log files?
> You could also try this
> Northwind and pubs Sample Databases for SQL Server 2000http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-035...
> Hope this helps,
> Ben Nevarez
> "Janis Rough" wrote:
> > I got SQL Server 2007 Express configured and I ran a few queries. It
> > appears to be configured correctly. I am trying to attach the
> > Northwind and Pubs example files from SQL Server 2000. I have tried
> > more than several times to attach them. I have them in the data file
> > of SQL Server 2007 express. The files are not read only. I looked
> > in the permissions and they were but I changed it to writeable and
> > checked the other files the log file and such.
> > When I try to attach the files I get the error message "Database
> > Northwind cannot be upgraded because it is read only or has read only
> > files. Make the files writeable and re-run recovery".
> > What is recovery? Do the files have to be upgraded from SQL SErver
> > 2000?
> > thanks,
sorry, it didn't help, I got the Northwind 2000 sample file. I didn't
know the latest version was 2005, so that was helpful, however, I did
change the permissions for all the files, the log file and the mdf
files. They still won't attach.|||On Jan 31, 8:16=A0am, Janis Rough <jlro...@.yahoo.com> wrote:
> On Jan 30, 7:10 pm, Ben Nevarez
>
>
> <bneva...@.no.spam.please.sunamerica.com> wrote:
> > Hi Janis,
> > Is that SQL Server 2005? (There is no SQL Server 2007). Have you changed= the
> > permissions of both data and log files?
> > You could also try this
> > Northwind and pubs Sample Databases for SQL Server 2000http://www.micros=
oft.com/downloads/details.aspx?FamilyID=3D06616212-035...
> > Hope this helps,
> > Ben Nevarez
> > "Janis Rough" wrote:
> > > I got SQL Server 2007 Express configured and I ran a few queries. It
> > > appears to be configured correctly. =A0 I am trying to attach the
> > > Northwind and Pubs example files from SQL Server 2000. =A0 I have trie=d
> > > more than several times to attach them. =A0I have them in the data fil=e
> > > of SQL Server 2007 express. =A0 The files are not read only. =A0I look=ed
> > > in the permissions and they were but I changed it to writeable and
> > > checked the other files the log file and such.
> > > When I try to attach the files I get the error message "Database
> > > Northwind cannot be upgraded because it is read only or has read only
> > > files. Make the files writeable and re-run recovery".
> > > What is recovery? =A0Do the files have to be upgraded from SQL SErver
> > > 2000?
> > > thanks,
> sorry, it didn't help, I got the Northwind 2000 sample file. =A0I didn't
> know the latest version was 2005, so that was helpful, however, I did
> change the permissions for all the files, the log file and the mdf
> files. =A0They still won't attach.- Hide quoted text -
> - Show quoted text -
Hi Janis,
There is no need to attach the northwind or pubs dbs . There are
scripts like instnwnd.sql which will configure the northwind db for
you . Just execute the script.
and don't try to attach the db from higher version to lower
version...!
Thanks
Ajay|||On Jan 30, 11:10 pm, Ajay Rengunthwar <aju...@.gmail.com> wrote:
> On Jan 31, 8:16 am, Janis Rough <jlro...@.yahoo.com> wrote:
>
> > On Jan 30, 7:10 pm, Ben Nevarez
> > <bneva...@.no.spam.please.sunamerica.com> wrote:
> > > Hi Janis,
> > > Is that SQL Server 2005? (There is no SQL Server 2007). Have you changed the
> > > permissions of both data and log files?
> > > You could also try this
> > > Northwind and pubs Sample Databases for SQL Server 2000http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-035...
> > > Hope this helps,
> > > Ben Nevarez
> > > "Janis Rough" wrote:
> > > > I got SQL Server 2007 Express configured and I ran a few queries. It
> > > > appears to be configured correctly. I am trying to attach the
> > > > Northwind and Pubs example files from SQL Server 2000. I have tried
> > > > more than several times to attach them. I have them in the data file
> > > > of SQL Server 2007 express. The files are not read only. I looked
> > > > in the permissions and they were but I changed it to writeable and
> > > > checked the other files the log file and such.
> > > > When I try to attach the files I get the error message "Database
> > > > Northwind cannot be upgraded because it is read only or has read only
> > > > files. Make the files writeable and re-run recovery".
> > > > What is recovery? Do the files have to be upgraded from SQL SErver
> > > > 2000?
> > > > thanks,
> > sorry, it didn't help, I got the Northwind 2000 sample file. I didn't
> > know the latest version was 2005, so that was helpful, however, I did
> > change the permissions for all the files, the log file and the mdf
> > files. They still won't attach.- Hide quoted text -
> > - Show quoted text -
> Hi Janis,
> There is no need to attach the northwind or pubs dbs . There are
> scripts like instnwnd.sql which will configure the northwind db for
> you . Just execute the script.
> and don't try to attach the db from higher version to lower
> version...!
> Thanks
> Ajay
Thanks,|||On Jan 30, 11:10 pm, Ajay Rengunthwar <aju...@.gmail.com> wrote:
> On Jan 31, 8:16 am, Janis Rough <jlro...@.yahoo.com> wrote:
>
> > On Jan 30, 7:10 pm, Ben Nevarez
> > <bneva...@.no.spam.please.sunamerica.com> wrote:
> > > Hi Janis,
> > > Is that SQL Server 2005? (There is no SQL Server 2007). Have you changed the
> > > permissions of both data and log files?
> > > You could also try this
> > > Northwind and pubs Sample Databases for SQL Server 2000http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-035...
> > > Hope this helps,
> > > Ben Nevarez
> > > "Janis Rough" wrote:
> > > > I got SQL Server 2007 Express configured and I ran a few queries. It
> > > > appears to be configured correctly. I am trying to attach the
> > > > Northwind and Pubs example files from SQL Server 2000. I have tried
> > > > more than several times to attach them. I have them in the data file
> > > > of SQL Server 2007 express. The files are not read only. I looked
> > > > in the permissions and they were but I changed it to writeable and
> > > > checked the other files the log file and such.
> > > > When I try to attach the files I get the error message "Database
> > > > Northwind cannot be upgraded because it is read only or has read only
> > > > files. Make the files writeable and re-run recovery".
> > > > What is recovery? Do the files have to be upgraded from SQL SErver
> > > > 2000?
> > > > thanks,
> > sorry, it didn't help, I got the Northwind 2000 sample file. I didn't
> > know the latest version was 2005, so that was helpful, however, I did
> > change the permissions for all the files, the log file and the mdf
> > files. They still won't attach.- Hide quoted text -
> > - Show quoted text -
> Hi Janis,
> There is no need to attach the northwind or pubs dbs . There are
> scripts like instnwnd.sql which will configure the northwind db for
> you . Just execute the script.
> and don't try to attach the db from higher version to lower
> version...!
> Thanks
> Ajay
It worked I created Northwind and Pubs successfully. I have one
question if you happen to see this. Why don't the two databases now
show up in the Object Explorer?|||> It worked I created Northwind and Pubs successfully. I have one
> question if you happen to see this. Why don't the two databases now
> show up in the Object Explorer?
Did you refresh? Or even disconnect and re-connect?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Janis Rough" <jlrough@.yahoo.com> wrote in message
news:f4fc0c53-e93a-47ff-8a94-561e04fb773a@.h11g2000prf.googlegroups.com...
> On Jan 30, 11:10 pm, Ajay Rengunthwar <aju...@.gmail.com> wrote:
>> On Jan 31, 8:16 am, Janis Rough <jlro...@.yahoo.com> wrote:
>>
>> > On Jan 30, 7:10 pm, Ben Nevarez
>> > <bneva...@.no.spam.please.sunamerica.com> wrote:
>> > > Hi Janis,
>> > > Is that SQL Server 2005? (There is no SQL Server 2007). Have you changed the
>> > > permissions of both data and log files?
>> > > You could also try this
>> > > Northwind and pubs Sample Databases for SQL Server
>> > > 2000http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-035...
>> > > Hope this helps,
>> > > Ben Nevarez
>> > > "Janis Rough" wrote:
>> > > > I got SQL Server 2007 Express configured and I ran a few queries. It
>> > > > appears to be configured correctly. I am trying to attach the
>> > > > Northwind and Pubs example files from SQL Server 2000. I have tried
>> > > > more than several times to attach them. I have them in the data file
>> > > > of SQL Server 2007 express. The files are not read only. I looked
>> > > > in the permissions and they were but I changed it to writeable and
>> > > > checked the other files the log file and such.
>> > > > When I try to attach the files I get the error message "Database
>> > > > Northwind cannot be upgraded because it is read only or has read only
>> > > > files. Make the files writeable and re-run recovery".
>> > > > What is recovery? Do the files have to be upgraded from SQL SErver
>> > > > 2000?
>> > > > thanks,
>> > sorry, it didn't help, I got the Northwind 2000 sample file. I didn't
>> > know the latest version was 2005, so that was helpful, however, I did
>> > change the permissions for all the files, the log file and the mdf
>> > files. They still won't attach.- Hide quoted text -
>> > - Show quoted text -
>> Hi Janis,
>> There is no need to attach the northwind or pubs dbs . There are
>> scripts like instnwnd.sql which will configure the northwind db for
>> you . Just execute the script.
>> and don't try to attach the db from higher version to lower
>> version...!
>> Thanks
>> Ajay
> It worked I created Northwind and Pubs successfully. I have one
> question if you happen to see this. Why don't the two databases now
> show up in the Object Explorer?sql

Can''t Aquire connections because OfflineMode is true

I am designing a SSIS package (source=Flat files,, destination =OLEDB) which is executing fine on my system but when I try to execute it on Server 2003 (x64)
it says Error: The AcquireConenction method call to connection manager "name of conn" failed with error code 0xC0014019 and
it says can't Aquire Connections because OfflineMode is true, while as I checked several times while saving the package it is set to false...

One of our developers had the same problem today... try this technet article. I think there are two places where you can set it. A config file and the project settings themselves.

http://technet.microsoft.com/en-us/library/ms137749.aspx

|||Error says OfflineMode is true, but actually it is not..
|||

One of my collegues ran into the same issue some time ago. After contacting MSF support and several rounds of troubleshooting, I think he ended up re-installing SSIS and the problem went away...Sorry, but I don't have more details.

|||

We encountered this issue some time ago. We incurred this error when trying to execute the 32bit DTEXEC (Program Files (x86)) on a 64bit machine.

As Rafael mentioned after contacting Microsoft support we simply reinstalled the client tools for SSIS and the problem was resolved. We were unable to identify the specific cause of the issue. I believe this is another very similar thread that was discussed a while back: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1557996&SiteID=1

|||I resolved the issue, I changed the connection settings and executed it using 64-bit dtexec from command prompt

Can''t Aquire connections because OfflineMode is true

One of our developers had the same problem today... try this technet article. I think there are two places where you can set it. A config file and the project settings themselves.

http://technet.microsoft.com/en-us/library/ms137749.aspx

|||Error says OfflineMode is true, but actually it is not..
|||

|||

We encountered this issue some time ago. We incurred this error when trying to execute the 32bit DTEXEC (Program Files (x86)) on a 64bit machine.

|||I resolved the issue, I changed the connection settings and executed it using 64-bit dtexec from command prompt

Can''t Aquire connections because OfflineMode is true

One of our developers had the same problem today... try this technet article. I think there are two places where you can set it. A config file and the project settings themselves.

http://technet.microsoft.com/en-us/library/ms137749.aspx

|||Error says OfflineMode is true, but actually it is not..
|||

|||

We encountered this issue some time ago. We incurred this error when trying to execute the 32bit DTEXEC (Program Files (x86)) on a 64bit machine.

|||I resolved the issue, I changed the connection settings and executed it using 64-bit dtexec from command prompt

Can''t Aquire connections because OfflineMode is true

One of our developers had the same problem today... try this technet article. I think there are two places where you can set it. A config file and the project settings themselves.

http://technet.microsoft.com/en-us/library/ms137749.aspx

|||Error says OfflineMode is true, but actually it is not..
|||

|||

We encountered this issue some time ago. We incurred this error when trying to execute the 32bit DTEXEC (Program Files (x86)) on a 64bit machine.

|||I resolved the issue, I changed the connection settings and executed it using 64-bit dtexec from command prompt

Can''t Aquire connections because OfflineMode is true

One of our developers had the same problem today... try this technet article. I think there are two places where you can set it. A config file and the project settings themselves.

http://technet.microsoft.com/en-us/library/ms137749.aspx

|||Error says OfflineMode is true, but actually it is not..
|||

|||

We encountered this issue some time ago. We incurred this error when trying to execute the 32bit DTEXEC (Program Files (x86)) on a 64bit machine.

|||I resolved the issue, I changed the connection settings and executed it using 64-bit dtexec from command prompt
sql

Can''t Aquire connections because OfflineMode is true

One of our developers had the same problem today... try this technet article. I think there are two places where you can set it. A config file and the project settings themselves.

http://technet.microsoft.com/en-us/library/ms137749.aspx

|||Error says OfflineMode is true, but actually it is not..
|||

|||

We encountered this issue some time ago. We incurred this error when trying to execute the 32bit DTEXEC (Program Files (x86)) on a 64bit machine.

|||I resolved the issue, I changed the connection settings and executed it using 64-bit dtexec from command prompt

Monday, March 19, 2012

cannt remove data file from user database

We are running SQL server 2003 with SP3. I'm trying to
shrink a data files with the emptyfile option so I can
eventually remove the file using the alter database
command. However, I get the following error message when I
run the alter database command:

Error: the file PRADATA4 cannot be removed because it is
not empty.

The file that I'm trying to remove still has 62 extents on it.
I looked MS Knowledge base 254253 and 279511 on this problem but they say it is corrected by SQL server 7.0 with service pack 3.
commands that I'm running are as follows:

1) USE PRA
DBCC TRACEON(8901)
DBCC SHRINKFILE ('PRADATA4', EMPTYFILE)
DBCC TRACEOFF(8901)

2) USE PRA
GO
ALTERDATABASE PRA
REMOVE FILE PRADATA4
GO

Can anyone help?

ThanksYou are shrinking one file, but removing another. This sounds like a sub-optimal approach to me.

-PatP|||Originally posted by Pat Phelan
You are shrinking one file, but removing another. This sounds like a sub-optimal approach to me.

-PatP
sorry,I write error,the commands are:
1) USE PRA
DBCC TRACEON(8901)
DBCC SHRINKFILE ('PRADATA4', EMPTYFILE)
DBCC TRACEOFF(8901)

2) USE PRA
GO
ALTERDATABASE PRA
REMOVE FILE PRADATA4
GO|||First make sure there are no open transactions that are stopping this piece of data from being moved and ensure to try with trace flags as specified in one of the KBAs.|||Originally posted by Satya
First make sure there are no open transactions that are stopping this piece of data from being moved and ensure to try with trace flags as specified in one of the KBAs.

How to check open transactions?Use sp_who2?|||DBCC OPENTRAN and refer to books online for more information.|||Originally posted by Satya
DBCC OPENTRAN and refer to books online for more information.
Hi Satya:
Yesterday , I restarted our database service,and used DBCC OPENTRAN to check there are no open transactions,but we still cann't remove the PRADATA4 from database.

Thanks|||Books Online (BOL) documents the REMOVE FILE option of ALTER DATABASE, but BOL doesn't tell you that you first need to run SHRINKFILE('file_name', EMPTYFILE). This command shrinks the file to 0MB of space used and ensures that you've removed all the objects from the file.|||Originally posted by Satya
Books Online (BOL) documents the REMOVE FILE option of ALTER DATABASE, but BOL doesn't tell you that you first need to run SHRINKFILE('file_name', EMPTYFILE). This command shrinks the file to 0MB of space used and ensures that you've removed all the objects from the file.
Hi Satya:
Thanks for your help,I have used KB 324432 to resolve the problem.

Sunday, March 11, 2012

Cannot view log for SQL Agent job (SQL2005)

Hi,
I've set up jobs and output files in SQL2005. When the job is completed, I
would like to check the output file by clicking VIEW button. However, the
button is grey-out.
The owner of the job is sa. What is other permission I missed ?
Thanks.Hi Johnny
"Johnny" wrote:
> Hi,
> I've set up jobs and output files in SQL2005. When the job is completed, I
> would like to check the output file by clicking VIEW button. However, the
> button is grey-out.
> The owner of the job is sa. What is other permission I missed ?
> Thanks.
According to Books Online it is not available in SQL 2005
From Books Online:
View
In Microsoft SQL Server 2005, this button is disabled for viewing output
files. Instead, use Notepad to view job step output files. In Microsoft SQL
Server 2000, this launches Notepad to view the output file.
John|||Hi John,
Thank you. Why leave the button there ?
Regards,
Johnny
"John Bell" wrote:
> Hi Johnny
> "Johnny" wrote:
> > Hi,
> >
> > I've set up jobs and output files in SQL2005. When the job is completed, I
> > would like to check the output file by clicking VIEW button. However, the
> > button is grey-out.
> >
> > The owner of the job is sa. What is other permission I missed ?
> >
> > Thanks.
> According to Books Online it is not available in SQL 2005
> From Books Online:
> View
> In Microsoft SQL Server 2005, this button is disabled for viewing output
> files. Instead, use Notepad to view job step output files. In Microsoft SQL
> Server 2000, this launches Notepad to view the output file.
>
> John
>|||Hi Johnny
"Johnny" wrote:
> Hi John,
> Thank you. Why leave the button there ?
> Regards,
> Johnny
> "John Bell" wrote:
> > According to Books Online it is not available in SQL 2005
> >
> > From Books Online:
> >
> > View
> > In Microsoft SQL Server 2005, this button is disabled for viewing output
> > files. Instead, use Notepad to view job step output files. In Microsoft SQL
> > Server 2000, this launches Notepad to view the output file.
> >
> >
> > John
> >
I guess that if you are looking at a SQL 2000 instance of SQL Agent then it
will bring up the log file in Notepad!!!!
John|||Yes, I always check the output file for any jobs in SQL2000 Agent job.
"John Bell" wrote:
> Hi Johnny
> "Johnny" wrote:
> > Hi John,
> >
> > Thank you. Why leave the button there ?
> >
> > Regards,
> > Johnny
> >
> > "John Bell" wrote:
> >
> > > According to Books Online it is not available in SQL 2005
> > >
> > > From Books Online:
> > >
> > > View
> > > In Microsoft SQL Server 2005, this button is disabled for viewing output
> > > files. Instead, use Notepad to view job step output files. In Microsoft SQL
> > > Server 2000, this launches Notepad to view the output file.
> > >
> > >
> > > John
> > >
> I guess that if you are looking at a SQL 2000 instance of SQL Agent then it
> will bring up the log file in Notepad!!!!
> John|||"John Bell" wrote:
> I guess that if you are looking at a SQL 2000 instance of SQL Agent then it
> will bring up the log file in Notepad!!!!
> John
And it does!!
John

Saturday, February 25, 2012

Cannot specify 'dimension' for: Maintenance plan remove files older than

OS: Windows Server 2003 Standard Edition. MSDE 2000.

SQL Server Enterprise Manager Version 8.0.

The Maintenance Plan Wizard will not allow me to specify a value for the 'Remove Files...' I can enter a number but there is nothing in the drop down e.g. 3 'DAYS' perhaps?

Create then plan and then edit - yields the same result.

Any ideas on why there is no 'dimension' for Remove Files Older than ?

Appreciate any tips, ideas, etc.

Thank.s

Anybody have issues trying to specify a dimension for 'Remove Files...' either during Maintenance Wizard (create) or afterwards - edit properties?

The drop down - where you would expect a list of values like: day, week, month - is completely blank?

|||

I'm experiencing the exact same problem with my MSDE database. I cannot specify anything as to days, weeks, etc. It also will take other information, such as scheduling, directory, extension name, and actually create the jobs correctly, but then when you go back to the Maintenance Plan, you find the information has disappeared. If you try to modify any other aspect of the plan, you get an error, because this information is now missing. This is the only MSDE db I have, all the rest are standard or enterprise and work normally from the same Enterprise Manager.

I have not found any explanation or work-around for this problem.

Cannot specify 'dimension' for: Maintenance plan remove files older than

OS: Windows Server 2003 Standard Edition. MSDE 2000.

SQL Server Enterprise Manager Version 8.0.

The Maintenance Plan Wizard will not allow me to specify a value for the 'Remove Files...' I can enter a number but there is nothing in the drop down e.g. 3 'DAYS' perhaps?

Create then plan and then edit - yields the same result.

Any ideas on why there is no 'dimension' for Remove Files Older than ?

Appreciate any tips, ideas, etc.

Thank.s

Anybody have issues trying to specify a dimension for 'Remove Files...' either during Maintenance Wizard (create) or afterwards - edit properties?

The drop down - where you would expect a list of values like: day, week, month - is completely blank?

|||

I have not found any explanation or work-around for this problem.

Friday, February 24, 2012

Cannot shrink log file 2 (emptydb_Log) because all logical log files are in use.

I am getting the following error message that is
preventing me from shrinking the log file for a
database. The database was created by doing a restore
from a backup of a database named emptydb. Prior to the
backup of emptydb, its transaction log had been backed up
and truncated, then a complete database backup of emptydb
had been done using the overwrite option in Enterprise
Manager | Tools | Backup Database.
After the restore of emptydb into a new database, the new
database was loaded and a large transaction file was
created. I did a backup of the complete new database,
then a backup of the transaction log, then attempted to
shrink the new database.
Why is there still an emptydb log file in this database?
How do I get the shrinkfile or shrinkdatase to truncate
the log file?
Thanks in advance for your help.
Jim DurbinIt sounds like the database was created with a "large" log file. If the
file size was set explicitly, as opposed to growing, you will need to use
the dbcc shrinkfile command to resize it. The dbcc shrink database will
only shrink the file to it's initial size at best.
If the dbcc shrinkfile does not actually shrink the file, this basically
means that you have active transactions in the trailing virtual log files so
it can't be truncated and dropped. You can take a look at your VLF's by
issuing a dbcc loginfo command, this shows how many VLFs you have and which
one(s) are active. You may have to issue several backup log commands before
the active log circles around to some of the first VLFs and then you can
drop the trailing ones, etc...
See "Truncating the Transaction Log" and "Shrinking the Transaction Log" in
Books Online for full details.
"Jim Durbin" <anonymous@.discussions.microsoft.com> wrote in message
news:22cd01c3d160$228ae760$3101280a@.phx.gbl...
> I am getting the following error message that is
> preventing me from shrinking the log file for a
> database. The database was created by doing a restore
> from a backup of a database named emptydb. Prior to the
> backup of emptydb, its transaction log had been backed up
> and truncated, then a complete database backup of emptydb
> had been done using the overwrite option in Enterprise
> Manager | Tools | Backup Database.
> After the restore of emptydb into a new database, the new
> database was loaded and a large transaction file was
> created. I did a backup of the complete new database,
> then a backup of the transaction log, then attempted to
> shrink the new database.
> Why is there still an emptydb log file in this database?
> How do I get the shrinkfile or shrinkdatase to truncate
> the log file?
> Thanks in advance for your help.
> Jim Durbin
>

Cannot shrink log file

Hello
I am using this command to shrink log files because the size of log files is
going to double from data files and taking all space but i am getting this
message so help me how can i stop these process and what is the save way to
save my data also.
use BygTest
go
exec sp_helpfile
go
dbcc shrinkfile(MBYGDB_log, 10)
go
Message is!
Cannot shrink log file 2 (MBYDB_Log) because all logical log files are in use.
Best regards,
--
Olav
Click SoftTo shrink the log file I use the following:
USE BygTest
CHECKPOINT
CHECKPOINT
CHECKPOINT
CHECKPOINT
CHECKPOINT
BACKUP LOG BygTest WITH TRUNCATE_ONLY
dbcc shrinkfile (MBYGDB_log,10)
but in reference to the error message, I don't know the reason :-(
good luck
Javi
"Olav" <Olav@.discussions.microsoft.com> escribió en el mensaje
news:8090B57B-7613-491E-836D-1F058B4C754F@.microsoft.com...
> Hello
> I am using this command to shrink log files because the size of log files
> is
> going to double from data files and taking all space but i am getting this
> message so help me how can i stop these process and what is the save way
> to
> save my data also.
> use BygTest
> go
> exec sp_helpfile
> go
> dbcc shrinkfile(MBYGDB_log, 10)
> go
> Message is!
> Cannot shrink log file 2 (MBYDB_Log) because all logical log files are in
> use.
> Best regards,
> --
> Olav
> Click Soft|||Thanks for good tip. i have one last question in this regards that if size of
my data file is 2 124 876 and size of log file is 4 248 987 and i shrink it
to for example 10 424 then what is effect on the database data?
--
Olav
Click Soft
"Javi" wrote:
> To shrink the log file I use the following:
> USE BygTest
> CHECKPOINT
> CHECKPOINT
> CHECKPOINT
> CHECKPOINT
> CHECKPOINT
> BACKUP LOG BygTest WITH TRUNCATE_ONLY
> dbcc shrinkfile (MBYGDB_log,10)
> but in reference to the error message, I don't know the reason :-(
> good luck
> Javi
> "Olav" <Olav@.discussions.microsoft.com> escribiÃ³ en el mensaje
> news:8090B57B-7613-491E-836D-1F058B4C754F@.microsoft.com...
> > Hello
> > I am using this command to shrink log files because the size of log files
> > is
> > going to double from data files and taking all space but i am getting this
> > message so help me how can i stop these process and what is the save way
> > to
> > save my data also.
> >
> > use BygTest
> > go
> >
> > exec sp_helpfile
> > go
> >
> > dbcc shrinkfile(MBYGDB_log, 10)
> > go
> >
> > Message is!
> > Cannot shrink log file 2 (MBYDB_Log) because all logical log files are in
> > use.
> >
> > Best regards,
> >
> > --
> > Olav
> > Click Soft
>
>|||Olav
If don't carry about the data then set your database to SIMPLE recovery
mode rather FULL .
With FULL recovery mode you will have ( in order to manage the LOG's size
and not to lose the data) BACKUP LOG file.
"Olav" <Olav@.discussions.microsoft.com> wrote in message
news:4C5ED48E-DFF3-4C76-864A-71AA9B83B56C@.microsoft.com...
> Thanks for good tip. i have one last question in this regards that if size
of
> my data file is 2 124 876 and size of log file is 4 248 987 and i shrink
it
> to for example 10 424 then what is effect on the database data?
> --
> Olav
> Click Soft
>
> "Javi" wrote:
> > To shrink the log file I use the following:
> >
> > USE BygTest
> > CHECKPOINT
> > CHECKPOINT
> > CHECKPOINT
> > CHECKPOINT
> > CHECKPOINT
> > BACKUP LOG BygTest WITH TRUNCATE_ONLY
> > dbcc shrinkfile (MBYGDB_log,10)
> >
> > but in reference to the error message, I don't know the reason :-(
> >
> > good luck
> >
> > Javi
> >
> > "Olav" <Olav@.discussions.microsoft.com> escribi? en el mensaje
> > news:8090B57B-7613-491E-836D-1F058B4C754F@.microsoft.com...
> > > Hello
> > > I am using this command to shrink log files because the size of log
files
> > > is
> > > going to double from data files and taking all space but i am getting
this
> > > message so help me how can i stop these process and what is the save
way
> > > to
> > > save my data also.
> > >
> > > use BygTest
> > > go
> > >
> > > exec sp_helpfile
> > > go
> > >
> > > dbcc shrinkfile(MBYGDB_log, 10)
> > > go
> > >
> > > Message is!
> > > Cannot shrink log file 2 (MBYDB_Log) because all logical log files are
in
> > > use.
> > >
> > > Best regards,
> > >
> > > --
> > > Olav
> > > Click Soft
> >
> >
> >|||The data will be still there, but you will be not able to undo changes in
the database, I mean, if you at 7:00 has a job that loads data in the
database and at 10:00 other job deletes some registers, using the log file
you can undo these, but if the data you have is good for you, you don't want
undo the changes, then you don't need to maintain the log file info in order
to undo these changes. Use backup to maintain safe your data. It's better.
HTH
Javi
"Olav" <Olav@.discussions.microsoft.com> escribió en el mensaje
news:4C5ED48E-DFF3-4C76-864A-71AA9B83B56C@.microsoft.com...
> Thanks for good tip. i have one last question in this regards that if size
> of
> my data file is 2 124 876 and size of log file is 4 248 987 and i shrink
> it
> to for example 10 424 then what is effect on the database data?
> --
> Olav
> Click Soft
>
> "Javi" wrote:
>> To shrink the log file I use the following:
>> USE BygTest
>> CHECKPOINT
>> CHECKPOINT
>> CHECKPOINT
>> CHECKPOINT
>> CHECKPOINT
>> BACKUP LOG BygTest WITH TRUNCATE_ONLY
>> dbcc shrinkfile (MBYGDB_log,10)
>> but in reference to the error message, I don't know the reason :-(
>> good luck
>> Javi
>> "Olav" <Olav@.discussions.microsoft.com> escribió en el mensaje
>> news:8090B57B-7613-491E-836D-1F058B4C754F@.microsoft.com...
>> > Hello
>> > I am using this command to shrink log files because the size of log
>> > files
>> > is
>> > going to double from data files and taking all space but i am getting
>> > this
>> > message so help me how can i stop these process and what is the save
>> > way
>> > to
>> > save my data also.
>> >
>> > use BygTest
>> > go
>> >
>> > exec sp_helpfile
>> > go
>> >
>> > dbcc shrinkfile(MBYGDB_log, 10)
>> > go
>> >
>> > Message is!
>> > Cannot shrink log file 2 (MBYDB_Log) because all logical log files are
>> > in
>> > use.
>> >
>> > Best regards,
>> >
>> > --
>> > Olav
>> > Click Soft
>>

Cannot shrink log file

Cannot shrink large database file

I have a server that is running SQL 2000. There is one large database 200
GB in size made of three files in the primary file group. We just freed up
40 GB of space in the db by archiving a table. The taskpad of SQL indicates
all this comes from the first file in the primary filegroup.
Our problem is that we've had a ton of trouble shrinking it down. We've
tried shrinking increments, with TRUNCATEONLY (after NOTRUNCATE). We
haven't done empty file because we do not want to remove the file. Out of
all of our attempts, only one try has succeeded in removing 10 GB, and that
took 4 hours. DBCC CHECKDB shows no errors, we did a backup successfully.
It just looks like it is running slow with high CPU and DISK I/O. The data
files are all on a fiber-optic SAN. Any suggestions? Thanks.

*************************************************
Andy S.
andymcdba1@.noreply.yahoo.com
Please remove "noreply" before replying.
*************************************************
The shrinking process usually is very painful and resource intensive. How
long it takes usually depends on the hardware and how much activity you have
in that file. In order to shrink the file SQL Server needs to move any data
pages near the end of the file towards the beginning of the file. This is a
fully logged operation and can be blocked just like any other process that
updates pages. 40GB in a 200GB db is not that much free space. You need
plenty of free space on a regular basis to handle things like reindexing
anyway. If you must shrink then use SHRINKFILE vs. SHRINKDATABASE and make
sure when you are done that all the files in the file group are the same
size. Have a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Andy S." <andymcdba1@.noreply.yahoo.com> wrote in message
news:%23D6br50bFHA.3048@.TK2MSFTNGP12.phx.gbl...
>I have a server that is running SQL 2000. There is one large database 200
>GB in size made of three files in the primary file group. We just freed up
>40 GB of space in the db by archiving a table. The taskpad of SQL
>indicates all this comes from the first file in the primary filegroup.
> Our problem is that we've had a ton of trouble shrinking it down. We've
> tried shrinking increments, with TRUNCATEONLY (after NOTRUNCATE). We
> haven't done empty file because we do not want to remove the file. Out of
> all of our attempts, only one try has succeeded in removing 10 GB, and
> that took 4 hours. DBCC CHECKDB shows no errors, we did a backup
> successfully. It just looks like it is running slow with high CPU and DISK
> I/O. The data files are all on a fiber-optic SAN. Any suggestions?
> Thanks.
>
> --
> *************************************************
> Andy S.
> andymcdba1@.noreply.yahoo.com
> Please remove "noreply" before replying.
> *************************************************
>

Cannot shrink large database file

I have a server that is running SQL 2000. There is one large database 200
GB in size made of three files in the primary file group. We just freed up
40 GB of space in the db by archiving a table. The taskpad of SQL indicates
all this comes from the first file in the primary filegroup.
Our problem is that we've had a ton of trouble shrinking it down. We've
tried shrinking increments, with TRUNCATEONLY (after NOTRUNCATE). We
haven't done empty file because we do not want to remove the file. Out of
all of our attempts, only one try has succeeded in removing 10 GB, and that
took 4 hours. DBCC CHECKDB shows no errors, we did a backup successfully.
It just looks like it is running slow with high CPU and DISK I/O. The data
files are all on a fiber-optic SAN. Any suggestions? Thanks.
****************************************
*********
Andy S.
andymcdba1@.noreply.yahoo.com
Please remove "noreply" before replying.
****************************************
*********The shrinking process usually is very painful and resource intensive. How
long it takes usually depends on the hardware and how much activity you have
in that file. In order to shrink the file SQL Server needs to move any data
pages near the end of the file towards the beginning of the file. This is a
fully logged operation and can be blocked just like any other process that
updates pages. 40GB in a 200GB db is not that much free space. You need
plenty of free space on a regular basis to handle things like reindexing
anyway. If you must shrink then use SHRINKFILE vs. SHRINKDATABASE and make
sure when you are done that all the files in the file group are the same
size. Have a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Andy S." <andymcdba1@.noreply.yahoo.com> wrote in message
news:%23D6br50bFHA.3048@.TK2MSFTNGP12.phx.gbl...
>I have a server that is running SQL 2000. There is one large database 200
>GB in size made of three files in the primary file group. We just freed up
>40 GB of space in the db by archiving a table. The taskpad of SQL
>indicates all this comes from the first file in the primary filegroup.
> Our problem is that we've had a ton of trouble shrinking it down. We've
> tried shrinking increments, with TRUNCATEONLY (after NOTRUNCATE). We
> haven't done empty file because we do not want to remove the file. Out of
> all of our attempts, only one try has succeeded in removing 10 GB, and
> that took 4 hours. DBCC CHECKDB shows no errors, we did a backup
> successfully. It just looks like it is running slow with high CPU and DISK
> I/O. The data files are all on a fiber-optic SAN. Any suggestions?
> Thanks.
>
> --
> ****************************************
*********
> Andy S.
> andymcdba1@.noreply.yahoo.com
> Please remove "noreply" before replying.
> ****************************************
*********
>

Cannot shrink large database file

I have a server that is running SQL 2000. There is one large database 200
GB in size made of three files in the primary file group. We just freed up
40 GB of space in the db by archiving a table. The taskpad of SQL indicates
all this comes from the first file in the primary filegroup.
Our problem is that we've had a ton of trouble shrinking it down. We've
tried shrinking increments, with TRUNCATEONLY (after NOTRUNCATE). We
haven't done empty file because we do not want to remove the file. Out of
all of our attempts, only one try has succeeded in removing 10 GB, and that
took 4 hours. DBCC CHECKDB shows no errors, we did a backup successfully.
It just looks like it is running slow with high CPU and DISK I/O. The data
files are all on a fiber-optic SAN. Any suggestions? Thanks.
--
*************************************************
Andy S.
andymcdba1@.noreply.yahoo.com
Please remove "noreply" before replying.
*************************************************The shrinking process usually is very painful and resource intensive. How
long it takes usually depends on the hardware and how much activity you have
in that file. In order to shrink the file SQL Server needs to move any data
pages near the end of the file towards the beginning of the file. This is a
fully logged operation and can be blocked just like any other process that
updates pages. 40GB in a 200GB db is not that much free space. You need
plenty of free space on a regular basis to handle things like reindexing
anyway. If you must shrink then use SHRINKFILE vs. SHRINKDATABASE and make
sure when you are done that all the files in the file group are the same
size. Have a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
--
Andrew J. Kelly SQL MVP
"Andy S." <andymcdba1@.noreply.yahoo.com> wrote in message
news:%23D6br50bFHA.3048@.TK2MSFTNGP12.phx.gbl...
>I have a server that is running SQL 2000. There is one large database 200
>GB in size made of three files in the primary file group. We just freed up
>40 GB of space in the db by archiving a table. The taskpad of SQL
>indicates all this comes from the first file in the primary filegroup.
> Our problem is that we've had a ton of trouble shrinking it down. We've
> tried shrinking increments, with TRUNCATEONLY (after NOTRUNCATE). We
> haven't done empty file because we do not want to remove the file. Out of
> all of our attempts, only one try has succeeded in removing 10 GB, and
> that took 4 hours. DBCC CHECKDB shows no errors, we did a backup
> successfully. It just looks like it is running slow with high CPU and DISK
> I/O. The data files are all on a fiber-optic SAN. Any suggestions?
> Thanks.
>
> --
> *************************************************
> Andy S.
> andymcdba1@.noreply.yahoo.com
> Please remove "noreply" before replying.
> *************************************************
>

Sunday, March 25, 2012

can't attach 2 sample files can attach other files

Can''t Aquire connections because OfflineMode is true

Can''t Aquire connections because OfflineMode is true

Can''t Aquire connections because OfflineMode is true

Can''t Aquire connections because OfflineMode is true

Can''t Aquire connections because OfflineMode is true

Can''t Aquire connections because OfflineMode is true

Monday, March 19, 2012

cannt remove data file from user database

Sunday, March 11, 2012

Cannot view log for SQL Agent job (SQL2005)

Cannot view log for SQL Agent job (SQL2005)

Cannot view log for SQL Agent job (SQL2005)

Saturday, February 25, 2012

Cannot specify 'dimension' for: Maintenance plan remove files older than

Cannot specify 'dimension' for: Maintenance plan remove files older than

Friday, February 24, 2012

Cannot shrink log file 2 (emptydb_Log) because all logical log files are in use.

Cannot shrink log file

Cannot shrink log file

Cannot shrink log file

Cannot shrink large database file

Cannot shrink large database file

Cannot shrink large database file

Cannot restore database

Blog Archive

About Me