DENY permission,SQL server: exists

Showing posts with label exists. Show all posts

Friday, March 9, 2012

deleted db but mdf file still exists on disk

Hi,
I deleted a database but the mdf and the log files still exists on disk, I
tried to deleted but they are locked.
What can I do?
Can I delete the entries on sysaltfiles table, delete mdf and log files and
reboot the sql server?
Thanks in advance
PedroI suggest you check to ensure the files are not being used by other
databases. Check the output of the following:
EXEC sp_MSforeachdb 'USE [?] EXEC sp_helpfile'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Pedro" <Pedro@.discussions.microsoft.com> wrote in message
news:14719753-DEEF-4AAF-A18D-157526941F14@.microsoft.com...
> Hi,
> I deleted a database but the mdf and the log files still exists on disk, I
> tried to deleted but they are locked.
> What can I do?
> Can I delete the entries on sysaltfiles table, delete mdf and log files
> and
> reboot the sql server?
> Thanks in advance
> Pedro
>|||Hi Dan,
I can't define the database on USE statment because that database (DR2006)
was deleted.
At deletion time I had a disabled job for that database, maybe this was the
cause for problem?
"Dan Guzman" wrote:
> I suggest you check to ensure the files are not being used by other
> databases. Check the output of the following:
> EXEC sp_MSforeachdb 'USE [?] EXEC sp_helpfile'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> news:14719753-DEEF-4AAF-A18D-157526941F14@.microsoft.com...
> > Hi,
> >
> > I deleted a database but the mdf and the log files still exists on disk, I
> > tried to deleted but they are locked.
> > What can I do?
> > Can I delete the entries on sysaltfiles table, delete mdf and log files
> > and
> > reboot the sql server?
> >
> > Thanks in advance
> >
> > Pedro
> >
>
>|||What version of SQL Server?
First check the system tables. Does the database exist in sysdatabases? Any reference to the
database in sysaltfiles?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pedro" <Pedro@.discussions.microsoft.com> wrote in message
news:1AD9CB8F-1303-4C5F-8A69-603DCA29847B@.microsoft.com...
> Hi Dan,
> I can't define the database on USE statment because that database (DR2006)
> was deleted.
> At deletion time I had a disabled job for that database, maybe this was the
> cause for problem?
> "Dan Guzman" wrote:
>> I suggest you check to ensure the files are not being used by other
>> databases. Check the output of the following:
>> EXEC sp_MSforeachdb 'USE [?] EXEC sp_helpfile'
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
>> news:14719753-DEEF-4AAF-A18D-157526941F14@.microsoft.com...
>> > Hi,
>> >
>> > I deleted a database but the mdf and the log files still exists on disk, I
>> > tried to deleted but they are locked.
>> > What can I do?
>> > Can I delete the entries on sysaltfiles table, delete mdf and log files
>> > and
>> > reboot the sql server?
>> >
>> > Thanks in advance
>> >
>> > Pedro
>> >
>>|||Hi Tibor,
I am using SQL Server 2000 SP4.
No, does not exist any reference in sysdatabases.
Pedro
"Tibor Karaszi" wrote:
> What version of SQL Server?
> First check the system tables. Does the database exist in sysdatabases? Any reference to the
> database in sysaltfiles?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> news:1AD9CB8F-1303-4C5F-8A69-603DCA29847B@.microsoft.com...
> > Hi Dan,
> >
> > I can't define the database on USE statment because that database (DR2006)
> > was deleted.
> > At deletion time I had a disabled job for that database, maybe this was the
> > cause for problem?
> >
> > "Dan Guzman" wrote:
> >
> >> I suggest you check to ensure the files are not being used by other
> >> databases. Check the output of the following:
> >>
> >> EXEC sp_MSforeachdb 'USE [?] EXEC sp_helpfile'
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> >> news:14719753-DEEF-4AAF-A18D-157526941F14@.microsoft.com...
> >> > Hi,
> >> >
> >> > I deleted a database but the mdf and the log files still exists on disk, I
> >> > tried to deleted but they are locked.
> >> > What can I do?
> >> > Can I delete the entries on sysaltfiles table, delete mdf and log files
> >> > and
> >> > reboot the sql server?
> >> >
> >> > Thanks in advance
> >> >
> >> > Pedro
> >> >
> >>
> >>
> >>
>|||What about sysaltfiles?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pedro" <Pedro@.discussions.microsoft.com> wrote in message
news:21004A82-C7FC-45A0-882E-8B0F2C3B7853@.microsoft.com...
> Hi Tibor,
> I am using SQL Server 2000 SP4.
> No, does not exist any reference in sysdatabases.
> Pedro
> "Tibor Karaszi" wrote:
>> What version of SQL Server?
>> First check the system tables. Does the database exist in sysdatabases? Any reference to the
>> database in sysaltfiles?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
>> news:1AD9CB8F-1303-4C5F-8A69-603DCA29847B@.microsoft.com...
>> > Hi Dan,
>> >
>> > I can't define the database on USE statment because that database (DR2006)
>> > was deleted.
>> > At deletion time I had a disabled job for that database, maybe this was the
>> > cause for problem?
>> >
>> > "Dan Guzman" wrote:
>> >
>> >> I suggest you check to ensure the files are not being used by other
>> >> databases. Check the output of the following:
>> >>
>> >> EXEC sp_MSforeachdb 'USE [?] EXEC sp_helpfile'
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
>> >> news:14719753-DEEF-4AAF-A18D-157526941F14@.microsoft.com...
>> >> > Hi,
>> >> >
>> >> > I deleted a database but the mdf and the log files still exists on disk, I
>> >> > tried to deleted but they are locked.
>> >> > What can I do?
>> >> > Can I delete the entries on sysaltfiles table, delete mdf and log files
>> >> > and
>> >> > reboot the sql server?
>> >> >
>> >> > Thanks in advance
>> >> >
>> >> > Pedro
>> >> >
>> >>
>> >>
>> >>
>>|||I think this is the problem.
sysaltfiles have entries for that database, i didn't find any other entry
anywhere for that database.
Pedro
"Tibor Karaszi" wrote:
> What about sysaltfiles?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> news:21004A82-C7FC-45A0-882E-8B0F2C3B7853@.microsoft.com...
> > Hi Tibor,
> >
> > I am using SQL Server 2000 SP4.
> > No, does not exist any reference in sysdatabases.
> >
> > Pedro
> >
> > "Tibor Karaszi" wrote:
> >
> >> What version of SQL Server?
> >>
> >> First check the system tables. Does the database exist in sysdatabases? Any reference to the
> >> database in sysaltfiles?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> >> news:1AD9CB8F-1303-4C5F-8A69-603DCA29847B@.microsoft.com...
> >> > Hi Dan,
> >> >
> >> > I can't define the database on USE statment because that database (DR2006)
> >> > was deleted.
> >> > At deletion time I had a disabled job for that database, maybe this was the
> >> > cause for problem?
> >> >
> >> > "Dan Guzman" wrote:
> >> >
> >> >> I suggest you check to ensure the files are not being used by other
> >> >> databases. Check the output of the following:
> >> >>
> >> >> EXEC sp_MSforeachdb 'USE [?] EXEC sp_helpfile'
> >> >>
> >> >> --
> >> >> Hope this helps.
> >> >>
> >> >> Dan Guzman
> >> >> SQL Server MVP
> >> >>
> >> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> >> >> news:14719753-DEEF-4AAF-A18D-157526941F14@.microsoft.com...
> >> >> > Hi,
> >> >> >
> >> >> > I deleted a database but the mdf and the log files still exists on disk, I
> >> >> > tried to deleted but they are locked.
> >> >> > What can I do?
> >> >> > Can I delete the entries on sysaltfiles table, delete mdf and log files
> >> >> > and
> >> >> > reboot the sql server?
> >> >> >
> >> >> > Thanks in advance
> >> >> >
> >> >> > Pedro
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
>|||Hmm, so below query returns a number of rows?
select * from master..sysaltfiles sa
WHERE NOT EXISTS(SELECT * FROM master..sysdatabases sd WHERE sa.dbid = sd.dbid)
If so, I really don't know. I haven't come across that before. Maybe open a ticket with MS Support,
just to be certain you do the right thing here.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pedro" <Pedro@.discussions.microsoft.com> wrote in message
news:8AA0D07C-FBC3-4A2A-8B32-C191F4014FE1@.microsoft.com...
>I think this is the problem.
> sysaltfiles have entries for that database, i didn't find any other entry
> anywhere for that database.
> Pedro
> "Tibor Karaszi" wrote:
>> What about sysaltfiles?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
>> news:21004A82-C7FC-45A0-882E-8B0F2C3B7853@.microsoft.com...
>> > Hi Tibor,
>> >
>> > I am using SQL Server 2000 SP4.
>> > No, does not exist any reference in sysdatabases.
>> >
>> > Pedro
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> What version of SQL Server?
>> >>
>> >> First check the system tables. Does the database exist in sysdatabases? Any reference to the
>> >> database in sysaltfiles?
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
>> >> news:1AD9CB8F-1303-4C5F-8A69-603DCA29847B@.microsoft.com...
>> >> > Hi Dan,
>> >> >
>> >> > I can't define the database on USE statment because that database (DR2006)
>> >> > was deleted.
>> >> > At deletion time I had a disabled job for that database, maybe this was the
>> >> > cause for problem?
>> >> >
>> >> > "Dan Guzman" wrote:
>> >> >
>> >> >> I suggest you check to ensure the files are not being used by other
>> >> >> databases. Check the output of the following:
>> >> >>
>> >> >> EXEC sp_MSforeachdb 'USE [?] EXEC sp_helpfile'
>> >> >>
>> >> >> --
>> >> >> Hope this helps.
>> >> >>
>> >> >> Dan Guzman
>> >> >> SQL Server MVP
>> >> >>
>> >> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
>> >> >> news:14719753-DEEF-4AAF-A18D-157526941F14@.microsoft.com...
>> >> >> > Hi,
>> >> >> >
>> >> >> > I deleted a database but the mdf and the log files still exists on disk, I
>> >> >> > tried to deleted but they are locked.
>> >> >> > What can I do?
>> >> >> > Can I delete the entries on sysaltfiles table, delete mdf and log files
>> >> >> > and
>> >> >> > reboot the sql server?
>> >> >> >
>> >> >> > Thanks in advance
>> >> >> >
>> >> >> > Pedro
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>>|||No, it returns 0 rows
"Tibor Karaszi" wrote:
> Hmm, so below query returns a number of rows?
> select * from master..sysaltfiles sa
> WHERE NOT EXISTS(SELECT * FROM master..sysdatabases sd WHERE sa.dbid = sd.dbid)
>
> If so, I really don't know. I haven't come across that before. Maybe open a ticket with MS Support,
> just to be certain you do the right thing here.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> news:8AA0D07C-FBC3-4A2A-8B32-C191F4014FE1@.microsoft.com...
> >I think this is the problem.
> > sysaltfiles have entries for that database, i didn't find any other entry
> > anywhere for that database.
> >
> > Pedro
> >
> > "Tibor Karaszi" wrote:
> >
> >> What about sysaltfiles?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> >> news:21004A82-C7FC-45A0-882E-8B0F2C3B7853@.microsoft.com...
> >> > Hi Tibor,
> >> >
> >> > I am using SQL Server 2000 SP4.
> >> > No, does not exist any reference in sysdatabases.
> >> >
> >> > Pedro
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> What version of SQL Server?
> >> >>
> >> >> First check the system tables. Does the database exist in sysdatabases? Any reference to the
> >> >> database in sysaltfiles?
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >> >>
> >> >>
> >> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> >> >> news:1AD9CB8F-1303-4C5F-8A69-603DCA29847B@.microsoft.com...
> >> >> > Hi Dan,
> >> >> >
> >> >> > I can't define the database on USE statment because that database (DR2006)
> >> >> > was deleted.
> >> >> > At deletion time I had a disabled job for that database, maybe this was the
> >> >> > cause for problem?
> >> >> >
> >> >> > "Dan Guzman" wrote:
> >> >> >
> >> >> >> I suggest you check to ensure the files are not being used by other
> >> >> >> databases. Check the output of the following:
> >> >> >>
> >> >> >> EXEC sp_MSforeachdb 'USE [?] EXEC sp_helpfile'
> >> >> >>
> >> >> >> --
> >> >> >> Hope this helps.
> >> >> >>
> >> >> >> Dan Guzman
> >> >> >> SQL Server MVP
> >> >> >>
> >> >> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> >> >> >> news:14719753-DEEF-4AAF-A18D-157526941F14@.microsoft.com...
> >> >> >> > Hi,
> >> >> >> >
> >> >> >> > I deleted a database but the mdf and the log files still exists on disk, I
> >> >> >> > tried to deleted but they are locked.
> >> >> >> > What can I do?
> >> >> >> > Can I delete the entries on sysaltfiles table, delete mdf and log files
> >> >> >> > and
> >> >> >> > reboot the sql server?
> >> >> >> >
> >> >> >> > Thanks in advance
> >> >> >> >
> >> >> >> > Pedro
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>|||Pedro,
The command I posted will not try to USE your deleted database. It will
list all files used by any database. After running it, check the file list
to ensure the files you are trying to delete are not used by other
databases.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Pedro" <Pedro@.discussions.microsoft.com> wrote in message
news:1AD9CB8F-1303-4C5F-8A69-603DCA29847B@.microsoft.com...
> Hi Dan,
> I can't define the database on USE statment because that database (DR2006)
> was deleted.
> At deletion time I had a disabled job for that database, maybe this was
> the
> cause for problem?
> "Dan Guzman" wrote:
>> I suggest you check to ensure the files are not being used by other
>> databases. Check the output of the following:
>> EXEC sp_MSforeachdb 'USE [?] EXEC sp_helpfile'
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
>> news:14719753-DEEF-4AAF-A18D-157526941F14@.microsoft.com...
>> > Hi,
>> >
>> > I deleted a database but the mdf and the log files still exists on
>> > disk, I
>> > tried to deleted but they are locked.
>> > What can I do?
>> > Can I delete the entries on sysaltfiles table, delete mdf and log files
>> > and
>> > reboot the sql server?
>> >
>> > Thanks in advance
>> >
>> > Pedro
>> >
>>|||Then how would you say that there exists a database in sysaltfiles and not in sysdatabases? Check
the id in column in sysaltfiles, and which row that correspond in sysdatabases. There is your
database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pedro" <Pedro@.discussions.microsoft.com> wrote in message
news:099D71F3-D668-4B2B-80C5-D1CB6B0B9104@.microsoft.com...
> No, it returns 0 rows
> "Tibor Karaszi" wrote:
>> Hmm, so below query returns a number of rows?
>> select * from master..sysaltfiles sa
>> WHERE NOT EXISTS(SELECT * FROM master..sysdatabases sd WHERE sa.dbid = sd.dbid)
>>
>> If so, I really don't know. I haven't come across that before. Maybe open a ticket with MS
>> Support,
>> just to be certain you do the right thing here.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
>> news:8AA0D07C-FBC3-4A2A-8B32-C191F4014FE1@.microsoft.com...
>> >I think this is the problem.
>> > sysaltfiles have entries for that database, i didn't find any other entry
>> > anywhere for that database.
>> >
>> > Pedro
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> What about sysaltfiles?
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
>> >> news:21004A82-C7FC-45A0-882E-8B0F2C3B7853@.microsoft.com...
>> >> > Hi Tibor,
>> >> >
>> >> > I am using SQL Server 2000 SP4.
>> >> > No, does not exist any reference in sysdatabases.
>> >> >
>> >> > Pedro
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> What version of SQL Server?
>> >> >>
>> >> >> First check the system tables. Does the database exist in sysdatabases? Any reference to
>> >> >> the
>> >> >> database in sysaltfiles?
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://www.solidqualitylearning.com/
>> >> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >> >>
>> >> >>
>> >> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
>> >> >> news:1AD9CB8F-1303-4C5F-8A69-603DCA29847B@.microsoft.com...
>> >> >> > Hi Dan,
>> >> >> >
>> >> >> > I can't define the database on USE statment because that database (DR2006)
>> >> >> > was deleted.
>> >> >> > At deletion time I had a disabled job for that database, maybe this was the
>> >> >> > cause for problem?
>> >> >> >
>> >> >> > "Dan Guzman" wrote:
>> >> >> >
>> >> >> >> I suggest you check to ensure the files are not being used by other
>> >> >> >> databases. Check the output of the following:
>> >> >> >>
>> >> >> >> EXEC sp_MSforeachdb 'USE [?] EXEC sp_helpfile'
>> >> >> >>
>> >> >> >> --
>> >> >> >> Hope this helps.
>> >> >> >>
>> >> >> >> Dan Guzman
>> >> >> >> SQL Server MVP
>> >> >> >>
>> >> >> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
>> >> >> >> news:14719753-DEEF-4AAF-A18D-157526941F14@.microsoft.com...
>> >> >> >> > Hi,
>> >> >> >> >
>> >> >> >> > I deleted a database but the mdf and the log files still exists on disk, I
>> >> >> >> > tried to deleted but they are locked.
>> >> >> >> > What can I do?
>> >> >> >> > Can I delete the entries on sysaltfiles table, delete mdf and log files
>> >> >> >> > and
>> >> >> >> > reboot the sql server?
>> >> >> >> >
>> >> >> >> > Thanks in advance
>> >> >> >> >
>> >> >> >> > Pedro
>> >> >> >> >
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>>|||It's true.
Some time ago I created a new empty database (000) so when doing restore the
first database wasn't overwritten be mistake.
Some how 000 database was using the mdf files of database that I deleted
(DR2006), I can't explain how this happened.
But it's fixed now.
Thank you very much
Pedro
"Tibor Karaszi" wrote:
> Then how would you say that there exists a database in sysaltfiles and not in sysdatabases? Check
> the id in column in sysaltfiles, and which row that correspond in sysdatabases. There is your
> database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> news:099D71F3-D668-4B2B-80C5-D1CB6B0B9104@.microsoft.com...
> > No, it returns 0 rows
> >
> > "Tibor Karaszi" wrote:
> >
> >> Hmm, so below query returns a number of rows?
> >>
> >> select * from master..sysaltfiles sa
> >> WHERE NOT EXISTS(SELECT * FROM master..sysdatabases sd WHERE sa.dbid = sd.dbid)
> >>
> >>
> >> If so, I really don't know. I haven't come across that before. Maybe open a ticket with MS
> >> Support,
> >> just to be certain you do the right thing here.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> >> news:8AA0D07C-FBC3-4A2A-8B32-C191F4014FE1@.microsoft.com...
> >> >I think this is the problem.
> >> > sysaltfiles have entries for that database, i didn't find any other entry
> >> > anywhere for that database.
> >> >
> >> > Pedro
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> What about sysaltfiles?
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >> >>
> >> >>
> >> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> >> >> news:21004A82-C7FC-45A0-882E-8B0F2C3B7853@.microsoft.com...
> >> >> > Hi Tibor,
> >> >> >
> >> >> > I am using SQL Server 2000 SP4.
> >> >> > No, does not exist any reference in sysdatabases.
> >> >> >
> >> >> > Pedro
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> >
> >> >> >> What version of SQL Server?
> >> >> >>
> >> >> >> First check the system tables. Does the database exist in sysdatabases? Any reference to
> >> >> >> the
> >> >> >> database in sysaltfiles?
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://www.solidqualitylearning.com/
> >> >> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >> >> >>
> >> >> >>
> >> >> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> >> >> >> news:1AD9CB8F-1303-4C5F-8A69-603DCA29847B@.microsoft.com...
> >> >> >> > Hi Dan,
> >> >> >> >
> >> >> >> > I can't define the database on USE statment because that database (DR2006)
> >> >> >> > was deleted.
> >> >> >> > At deletion time I had a disabled job for that database, maybe this was the
> >> >> >> > cause for problem?
> >> >> >> >
> >> >> >> > "Dan Guzman" wrote:
> >> >> >> >
> >> >> >> >> I suggest you check to ensure the files are not being used by other
> >> >> >> >> databases. Check the output of the following:
> >> >> >> >>
> >> >> >> >> EXEC sp_MSforeachdb 'USE [?] EXEC sp_helpfile'
> >> >> >> >>
> >> >> >> >> --
> >> >> >> >> Hope this helps.
> >> >> >> >>
> >> >> >> >> Dan Guzman
> >> >> >> >> SQL Server MVP
> >> >> >> >>
> >> >> >> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> >> >> >> >> news:14719753-DEEF-4AAF-A18D-157526941F14@.microsoft.com...
> >> >> >> >> > Hi,
> >> >> >> >> >
> >> >> >> >> > I deleted a database but the mdf and the log files still exists on disk, I
> >> >> >> >> > tried to deleted but they are locked.
> >> >> >> >> > What can I do?
> >> >> >> >> > Can I delete the entries on sysaltfiles table, delete mdf and log files
> >> >> >> >> > and
> >> >> >> >> > reboot the sql server?
> >> >> >> >> >
> >> >> >> >> > Thanks in advance
> >> >> >> >> >
> >> >> >> >> > Pedro
> >> >> >> >> >
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>|||Sorry,
I imagined that [?] was to be replaced by the database name.
I have fixed the problem.
Thank you.
All the best.
Pedro
"Dan Guzman" wrote:
> Pedro,
> The command I posted will not try to USE your deleted database. It will
> list all files used by any database. After running it, check the file list
> to ensure the files you are trying to delete are not used by other
> databases.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> news:1AD9CB8F-1303-4C5F-8A69-603DCA29847B@.microsoft.com...
> > Hi Dan,
> >
> > I can't define the database on USE statment because that database (DR2006)
> > was deleted.
> > At deletion time I had a disabled job for that database, maybe this was
> > the
> > cause for problem?
> >
> > "Dan Guzman" wrote:
> >
> >> I suggest you check to ensure the files are not being used by other
> >> databases. Check the output of the following:
> >>
> >> EXEC sp_MSforeachdb 'USE [?] EXEC sp_helpfile'
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> >> news:14719753-DEEF-4AAF-A18D-157526941F14@.microsoft.com...
> >> > Hi,
> >> >
> >> > I deleted a database but the mdf and the log files still exists on
> >> > disk, I
> >> > tried to deleted but they are locked.
> >> > What can I do?
> >> > Can I delete the entries on sysaltfiles table, delete mdf and log files
> >> > and
> >> > reboot the sql server?
> >> >
> >> > Thanks in advance
> >> >
> >> > Pedro
> >> >
> >>
> >>
> >>
>
>

Friday, March 9, 2012

deleted db but mdf file still exists on disk

deleted db but mdf file still exists on disk

deleted db but mdf file still exists on disk

DENY permission,SQL server

Blog Archive

About Me